Find the collation of all tables within a mysql database

Needing to find a collation of a mysql database for a migration I went to google. Found several options, but the best was one from stack overflow.


SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME

FROM INFORMATION_SCHEMA.COLUMNS;

This will output something like:


mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME
 -> FROM INFORMATION_SCHEMA.COLUMNS;
+---------------+--------------------+---------------------------------------+-------------------------------+-------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+---------------+--------------------+---------------------------------------+-------------------------------+-------------------+
| NULL | information_schema | CHARACTER_SETS | CHARACTER_SET_NAME | utf8_general_ci |
| NULL | information_schema | CHARACTER_SETS | DEFAULT_COLLATE_NAME | utf8_general_ci |
| NULL | information_schema | CHARACTER_SETS | DESCRIPTION | utf8_general_ci |
| NULL | information_schema | CHARACTER_SETS | MAXLEN | NULL |
| NULL | information_schema | COLLATIONS | COLLATION_NAME | utf8_general_ci |
| NULL | information_schema | COLLATIONS | CHARACTER_SET_NAME | utf8_general_ci |
| NULL | information_schema | COLLATIONS | ID | NULL |
| NULL | information_schema | COLLATIONS | IS_DEFAULT | utf8_general_ci |
| NULL | information_schema | COLLATIONS | IS_COMPILED | utf8_general_ci |
| NULL | information_schema | COLLATIONS | SORTLEN | NULL |
| NULL | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | COLLATION_NAME | utf8_general_ci |
| NULL | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | CHARACTER_SET_NAME | utf8_general_ci |
| NULL | information_schema | COLUMNS | TABLE_CATALOG | utf8_general_ci |
| NULL | information_schema | COLUMNS | TABLE_SCHEMA | utf8_general_ci |

[...]

| NULL | information_schema | VIEWS | SECURITY_TYPE | utf8_general_ci |
| NULL | information_schema | VIEWS | CHARACTER_SET_CLIENT | utf8_general_ci |
| NULL | information_schema | VIEWS | COLLATION_CONNECTION | utf8_general_ci |
| NULL | test_db_wp | wp_commentmeta | meta_id | NULL |
| NULL | test_db_wp | wp_commentmeta | comment_id | NULL |
| NULL | test_db_wp | wp_commentmeta | meta_key | utf8_general_ci |
| NULL | test_db_wp | wp_commentmeta | meta_value | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_ID | NULL |
| NULL | test_db_wp | wp_comments | comment_post_ID | NULL |
| NULL | test_db_wp | wp_comments | comment_author | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_author_email | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_author_url | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_author_IP | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_date | NULL |
| NULL | test_db_wp | wp_comments | comment_date_gmt | NULL |
| NULL | test_db_wp | wp_comments | comment_content | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_karma | NULL |
| NULL | test_db_wp | wp_comments | comment_approved | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_agent | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_type | utf8_general_ci |
| NULL | test_db_wp | wp_comments | comment_parent | NULL |
| NULL | test_db_wp | wp_comments | user_id | NULL |
| NULL | test_db_wp | wp_links | link_id | NULL |
| NULL | test_db_wp | wp_links | link_url | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_name | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_image | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_target | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_category | NULL |
| NULL | test_db_wp | wp_links | link_description | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_visible | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_owner | NULL |
| NULL | test_db_wp | wp_links | link_rating | NULL |
| NULL | test_db_wp | wp_links | link_updated | NULL |
| NULL | test_db_wp | wp_links | link_rel | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_notes | utf8_general_ci |
| NULL | test_db_wp | wp_links | link_rss | utf8_general_ci |
| NULL | test_db_wp | wp_options | option_id | NULL |
| NULL | test_db_wp | wp_options | option_name | utf8_general_ci |
| NULL | test_db_wp | wp_options | option_value | utf8_general_ci |

The first part of this shows the overall database collation. In this case, it is utf8_general_ci.

Then the second part shows the collation per column with each table within the database. In this case they are all the same as the default. However, it should be noted that this isn’t always the case. It can be different then the default.

How to find mysql variables with a query, specifically data directory

I needed to find out the data directory for mysql. A quick way to do this within mysql is to use the show variables command.


mysql> show variables;

Which will output something like:


+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |

[ .......]

| version_comment | (Ubuntu) |
| version_compile_machine | i686 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
+-----------------------------------------+-------------------------------------------------------------------------------------------+

You’ll see on this development server the data directory was in¬†/var/lib/mysql/

Query to insert all countries into database

I needed to insert all of the countries in the world into a database. I couldn’t find the query to run, so I created my own. Here it is. ‘country’ is the table name and ‘name’ is the column name.

INSERT INTO country

(name)

VALUES

(‘Afghanistan’),
(‘Albania’),
(‘Algeria’),
(‘Andorra’),
(‘Angola’),
(‘Antigua and Barbuda’),
(‘Argentina’),
(‘Armenia’),
(‘Australia’),
(‘Austria’),
(‘Azerbaijan’),
(‘Bahamas, The’),
(‘Bahrain’),
(‘Bangladesh’),
(‘Barbados’),
(‘Belarus’),
(‘Belgium’),
(‘Belize’),
(‘Benin’),
(‘Bhutan’),
(‘Bolivia’),
(‘Bosnia and Herzegovina’),
(‘Botswana’),
(‘Brazil’),
(‘Brunei’),
(‘Bulgaria’),
(‘Burkina Faso’),
(‘Burma’),
(‘Burundi’),
(‘Cambodia’),
(‘Cameroon’),
(‘Canada’),
(‘Cape Verde’),
(‘Central Africa’),
(‘Chad’),
(‘Chile’),
(‘China’),
(‘Colombia’),
(‘Comoros’),
(‘Congo, Democratic Republic of the’),
(‘Costa Rica’),
(‘Cote dIvoire’),
(‘Crete’),
(‘Croatia’),
(‘Cuba’),
(‘Cyprus’),
(‘Czech Republic’),
(‘Denmark’),
(‘Djibouti’),
(‘Dominican Republic’),
(‘East Timor’),
(‘Ecuador’),
(‘Egypt’),
(‘El Salvador’),
(‘Equatorial Guinea’),
(‘Eritrea’),
(‘Estonia’),
(‘Ethiopia’),
(‘Fiji’),
(‘Finland’),
(‘France’),
(‘Gabon’),
(‘Gambia, The’),
(‘Georgia’),
(‘Germany’),
(‘Ghana’),
(‘Greece’),
(‘Grenada’),
(‘Guadeloupe’),
(‘Guatemala’),
(‘Guinea’),
(‘Guinea-Bissau’),
(‘Guyana’),
(‘Haiti’),
(‘Holy See’),
(‘Honduras’),
(‘Hong Kong’),
(‘Hungary’),
(‘Iceland’),
(‘India’),
(‘Indonesia’),
(‘Iran’),
(‘Iraq’),
(‘Ireland’),
(‘Israel’),
(‘Italy’),
(‘Ivory Coast’),
(‘Jamaica’),
(‘Japan’),
(‘Jordan’),
(‘Kazakhstan’),
(‘Kenya’),
(‘Kiribati’),
(‘Korea, North’),
(‘Korea, South’),
(‘Kosovo’),
(‘Kuwait’),
(‘Kyrgyzstan’),
(‘Laos’),
(‘Latvia’),
(‘Lebanon’),
(‘Lesotho’),
(‘Liberia’),
(‘Libya’),
(‘Liechtenstein’),
(‘Lithuania’),
(‘Macau’),
(‘Macedonia’),
(‘Madagascar’),
(‘Malawi’),
(‘Malaysia’),
(‘Maldives’),
(‘Mali’),
(‘Malta’),
(‘Marshall Islands’),
(‘Mauritania’),
(‘Mauritius’),
(‘Mexico’),
(‘Micronesia’),
(‘Moldova’),
(‘Monaco’),
(‘Mongolia’),
(‘Montenegro’),
(‘Morocco’),
(‘Mozambique’),
(‘Namibia’),
(‘Nauru’),
(‘Nepal’),
(‘Netherlands’),
(‘New Zealand’),
(‘Nicaragua’),
(‘Niger’),
(‘Nigeria’),
(‘North Korea’),
(‘Norway’),
(‘Oman’),
(‘Pakistan’),
(‘Palau’),
(‘Panama’),
(‘Papua New Guinea’),
(‘Paraguay’),
(‘Peru’),
(‘Philippines’),
(‘Poland’),
(‘Portugal’),
(‘Qatar’),
(‘Romania’),
(‘Russia’),
(‘Rwanda’),
(‘Saint Lucia’),
(‘Saint Vincent and the Grenadines’),
(‘Samoa’),
(‘San Marino’),
(‘Sao Tome and Principe’),
(‘Saudi Arabia’),
(‘Scotland’),
(‘Senegal’),
(‘Serbia’),
(‘Seychelles’),
(‘Sierra Leone’),
(‘Singapore’),
(‘Slovakia’),
(‘Slovenia’),
(‘Solomon Islands’),
(‘Somalia’),
(‘South Africa’),
(‘South Korea’),
(‘Spain’),
(‘Sri Lanka’),
(‘Sudan’),
(‘Suriname’),
(‘Swaziland’),
(‘Sweden’),
(‘Switzerland’),
(‘Syria’),
(‘Taiwan’),
(‘Tajikistan’),
(‘Tanzania’),
(‘Thailand’),
(‘Tibet’),
(‘Timor-Leste’),
(‘Togo’),
(‘Tonga’),
(‘Trinidad and Tobago’),
(‘Tunisia’),
(‘Turkey’),
(‘Turkmenistan’),
(‘Tuvalu’),
(‘Uganda’),
(‘Ukraine’),
(‘United Arab Emirates’),
(‘United Kingdom’),
(‘United States’),
(‘Uruguay’),
(‘Uzbekistan’),
(‘Vanuatu’),
(‘Venezuela’),
(‘Vietnam’),
(‘Yemen’),
(‘Zambia’),
(‘Zimbabwe’);