Header RSS Feed
 
If you only want to see the articles of a certain category, please click on the desired category below:
ALL Android Backup BSD Database Hacks Hardware Internet Linux Mail MySQL Monitoring Network Personal PHP Proxy Shell Solaris Unix Virtualization VMware Windows Wyse

Quickly show character sets and collations on a MySQL database
Wednesday - May 30th 2018 - by - (0 comments)

To find out which character sets and collations all tables of a given database (here: rancher_dev) use, the following query helps:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLLATION_NAME, CHARACTER_SET_NAME FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "rancher_dev";
+--------------+-----------------------------------------------+-------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME                                    | COLLATION_NAME    | CHARACTER_SET_NAME |
+--------------+-----------------------------------------------+-------------------+--------------------+
| rancher_dev  | DATABASECHANGELOG                             | latin1_swedish_ci | latin1             |
| rancher_dev  | DATABASECHANGELOGLOCK                         | latin1_swedish_ci | latin1             |
| rancher_dev  | account                                       | latin1_swedish_ci | latin1             |
| rancher_dev  | account_link                                  | latin1_swedish_ci | latin1             |
| rancher_dev  | agent                                         | latin1_swedish_ci | latin1             |
| rancher_dev  | agent_group                                   | latin1_swedish_ci | latin1             |
| rancher_dev  | audit_log                                     | latin1_swedish_ci | latin1             |
| rancher_dev  | auth_token                                    | latin1_swedish_ci | latin1             |
| rancher_dev  | backup                                        | latin1_swedish_ci | latin1             |
| rancher_dev  | backup_target                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | catalog                                       | latin1_swedish_ci | latin1             |
| rancher_dev  | catalog_category                              | latin1_swedish_ci | latin1             |
| rancher_dev  | catalog_file                                  | latin1_swedish_ci | latin1             |
| rancher_dev  | catalog_label                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | catalog_template                              | latin1_swedish_ci | latin1             |
| rancher_dev  | catalog_template_category                     | latin1_swedish_ci | latin1             |
| rancher_dev  | catalog_version                               | latin1_swedish_ci | latin1             |
| rancher_dev  | catalog_version_label                         | latin1_swedish_ci | latin1             |
| rancher_dev  | certificate                                   | latin1_swedish_ci | latin1             |
| rancher_dev  | cluster_host_map                              | latin1_swedish_ci | latin1             |
| rancher_dev  | cluster_membership                            | latin1_swedish_ci | latin1             |
| rancher_dev  | config_item                                   | latin1_swedish_ci | latin1             |
| rancher_dev  | config_item_status                            | latin1_swedish_ci | latin1             |
| rancher_dev  | container_event                               | latin1_swedish_ci | latin1             |
| rancher_dev  | credential                                    | latin1_swedish_ci | latin1             |
| rancher_dev  | credential_instance_map                       | latin1_swedish_ci | latin1             |
| rancher_dev  | data                                          | latin1_swedish_ci | latin1             |
| rancher_dev  | deployment_unit                               | latin1_swedish_ci | latin1             |
| rancher_dev  | dynamic_schema                                | latin1_swedish_ci | latin1             |
| rancher_dev  | dynamic_schema_role                           | latin1_swedish_ci | latin1             |
| rancher_dev  | environment                                   | latin1_swedish_ci | latin1             |
| rancher_dev  | external_event                                | latin1_swedish_ci | latin1             |
| rancher_dev  | external_handler                              | latin1_swedish_ci | latin1             |
| rancher_dev  | external_handler_external_handler_process_map | latin1_swedish_ci | latin1             |
| rancher_dev  | external_handler_process                      | latin1_swedish_ci | latin1             |
| rancher_dev  | generic_object                                | latin1_swedish_ci | latin1             |
| rancher_dev  | global_load_balancer                          | latin1_swedish_ci | latin1             |
| rancher_dev  | healthcheck_instance                          | latin1_swedish_ci | latin1             |
| rancher_dev  | healthcheck_instance_host_map                 | latin1_swedish_ci | latin1             |
| rancher_dev  | host                                          | latin1_swedish_ci | latin1             |
| rancher_dev  | host_ip_address_map                           | latin1_swedish_ci | latin1             |
| rancher_dev  | host_label_map                                | latin1_swedish_ci | latin1             |
| rancher_dev  | host_template                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | host_vnet_map                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | image                                         | latin1_swedish_ci | latin1             |
| rancher_dev  | image_storage_pool_map                        | latin1_swedish_ci | latin1             |
| rancher_dev  | instance                                      | latin1_swedish_ci | latin1             |
| rancher_dev  | instance_host_map                             | latin1_swedish_ci | latin1             |
| rancher_dev  | instance_label_map                            | latin1_swedish_ci | latin1             |
| rancher_dev  | instance_link                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | ip_address                                    | latin1_swedish_ci | latin1             |
| rancher_dev  | ip_address_nic_map                            | latin1_swedish_ci | latin1             |
| rancher_dev  | ip_association                                | latin1_swedish_ci | latin1             |
| rancher_dev  | ip_pool                                       | latin1_swedish_ci | latin1             |
| rancher_dev  | label                                         | latin1_swedish_ci | latin1             |
| rancher_dev  | load_balancer                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | load_balancer_certificate_map                 | latin1_swedish_ci | latin1             |
| rancher_dev  | load_balancer_config                          | latin1_swedish_ci | latin1             |
| rancher_dev  | load_balancer_config_listener_map             | latin1_swedish_ci | latin1             |
| rancher_dev  | load_balancer_host_map                        | latin1_swedish_ci | latin1             |
| rancher_dev  | load_balancer_listener                        | latin1_swedish_ci | latin1             |
| rancher_dev  | load_balancer_target                          | latin1_swedish_ci | latin1             |
| rancher_dev  | machine_driver                                | latin1_swedish_ci | latin1             |
| rancher_dev  | mount                                         | latin1_swedish_ci | latin1             |
| rancher_dev  | network                                       | latin1_swedish_ci | latin1             |
| rancher_dev  | network_driver                                | latin1_swedish_ci | latin1             |
| rancher_dev  | network_service                               | latin1_swedish_ci | latin1             |
| rancher_dev  | network_service_provider                      | latin1_swedish_ci | latin1             |
| rancher_dev  | network_service_provider_instance_map         | latin1_swedish_ci | latin1             |
| rancher_dev  | nic                                           | latin1_swedish_ci | latin1             |
| rancher_dev  | offering                                      | latin1_swedish_ci | latin1             |
| rancher_dev  | physical_host                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | port                                          | latin1_swedish_ci | latin1             |
| rancher_dev  | process_execution                             | latin1_swedish_ci | latin1             |
| rancher_dev  | process_instance                              | latin1_swedish_ci | latin1             |
| rancher_dev  | project_member                                | latin1_swedish_ci | latin1             |
| rancher_dev  | project_template                              | latin1_swedish_ci | latin1             |
| rancher_dev  | resource_pool                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | scheduled_upgrade                             | latin1_swedish_ci | latin1             |
| rancher_dev  | secret                                        | latin1_swedish_ci | latin1             |
| rancher_dev  | service                                       | latin1_swedish_ci | latin1             |
| rancher_dev  | service_consume_map                           | latin1_swedish_ci | latin1             |
| rancher_dev  | service_event                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | service_expose_map                            | latin1_swedish_ci | latin1             |
| rancher_dev  | service_index                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | service_log                                   | latin1_swedish_ci | latin1             |
| rancher_dev  | setting                                       | latin1_swedish_ci | latin1             |
| rancher_dev  | snapshot                                      | latin1_swedish_ci | latin1             |
| rancher_dev  | snapshot_storage_pool_map                     | latin1_swedish_ci | latin1             |
| rancher_dev  | storage_driver                                | latin1_swedish_ci | latin1             |
| rancher_dev  | storage_pool                                  | latin1_swedish_ci | latin1             |
| rancher_dev  | storage_pool_host_map                         | latin1_swedish_ci | latin1             |
| rancher_dev  | subnet                                        | latin1_swedish_ci | latin1             |
| rancher_dev  | subnet_vnet_map                               | latin1_swedish_ci | latin1             |
| rancher_dev  | task                                          | latin1_swedish_ci | latin1             |
| rancher_dev  | task_instance                                 | latin1_swedish_ci | latin1             |
| rancher_dev  | ui_challenge                                  | latin1_swedish_ci | latin1             |
| rancher_dev  | user_preference                               | latin1_swedish_ci | latin1             |
| rancher_dev  | vnet                                          | latin1_swedish_ci | latin1             |
| rancher_dev  | volume                                        | latin1_swedish_ci | latin1             |
| rancher_dev  | volume_storage_pool_map                       | latin1_swedish_ci | latin1             |
| rancher_dev  | volume_template                               | latin1_swedish_ci | latin1             |
| rancher_dev  | zone                                          | latin1_swedish_ci | latin1             |
| rancher_dev  | cluster                                       | utf8_general_ci   | utf8               |
+--------------+-----------------------------------------------+-------------------+--------------------+
104 rows in set (0.01 sec)

To adapt the query to your own database, simply replace "rancher_dev" by your database name.

Based on the information found on https://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is .

 

Add a comment

Show form to leave a comment

Comments (newest first):

No comments yet.

Go to Homepage home
Linux Howtos how to's
Monitoring Plugins monitoring plugins
Links links

Valid HTML 4.01 Transitional
Valid CSS!
[Valid RSS]

7092 Days
until Death of Computers
Why?