Convert columns to default table / database

Question

Every single post I've seen for this involves running the following SQL:

ALTER TABLE <tablename> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

The problem with this, if I am wrong, is that it explicitly points to column mappings, so you get something like this when you mysqldump in the database:

  `address` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, `city` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, `state` varchar(2) COLLATE utf8_unicode_ci DEFAULT NULL, `zipcode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL, 

My question is: is there no way to convert a column to a default table or database without doing this?

For example, I have tables that might look like this:

  `address` varchar(150) DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `state` varchar(2) COLLATE utf8_general_ci DEFAULT NULL, `zipcode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL, 

I want to convert all columns to utf8_unicode_ci (default table / database), but not every column explicitly specified this sort, so when I mysqldump the converted table, it looks something like this:

  `address` varchar(150) DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `state` varchar(2) DEFAULT NULL, `zipcode` varchar(10) DEFAULT NULL, 

with a line at the end of the create table statement that defines the default character set and sort: ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

If your table or column is different from the default MySQL, in my case latin1_sweedish_ci, it will print the mapping to the column. See the following experiments demonstrating this.

To set the default character set, see this post .

First, let's create a database with two tables. One table has a character set and a mapping job.

 mysql> create database SO; mysql> use SO; mysql> create table test1 (col1 text, col2 text); mysql> create table test2 (col1 text, col2 text) character set utf8 collate utf8_unicode_ci; 

Now check show create table to see what it looks like:

 mysql> show create table test1; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test1 | CREATE TABLE `test1` ( `col1` text, `col2` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +-------+-----------------+ 1 row in set (0.00 sec) mysql> show create table test2; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test2 | CREATE TABLE `test2` ( `col1` text COLLATE utf8_unicode_ci, `col2` text COLLATE utf8_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci +-------+-----------------+ 1 row in set (0.00 sec) 

We can see that test2 already looks like the columns are specific and not used by default. I suspect that if it is different from MySQL by default, it will list it, and not different from the default table. Now let's see how they look in the information_schema database.

 mysql> select table_schema, table_name, table_collation from information_schema.tables where table_schema = 'SO'; +--------------+------------+-------------------+ | table_schema | table_name | table_collation | +--------------+------------+-------------------+ | SO | test1 | latin1_swedish_ci | | SO | test2 | utf8_unicode_ci | +--------------+------------+-------------------+ 2 rows in set (0.00 sec) mysql> select table_schema, table_name, column_name, character_set_name, collation_name from information_schema.columns where table_schema = 'SO'; +--------------+------------+-------------+--------------------+-------------------+ | table_schema | table_name | column_name | character_set_name | collation_name | +--------------+------------+-------------+--------------------+-------------------+ | SO | test1 | col1 | latin1 | latin1_swedish_ci | | SO | test1 | col2 | latin1 | latin1_swedish_ci | | SO | test2 | col1 | utf8 | utf8_unicode_ci | | SO | test2 | col2 | utf8 | utf8_unicode_ci | +--------------+------------+-------------+--------------------+-------------------+ 4 rows in set (0.00 sec) 

It appears that the columns have a specific character set and collation, regardless of whether we specified it. Let's upgrade test1 to the preferred character set and sort and see what happens.

 mysql> ALTER TABLE test1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test1; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test1 | CREATE TABLE `test1` ( `col1` mediumtext COLLATE utf8_unicode_ci, `col2` mediumtext COLLATE utf8_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci +-------+-----------------+ 1 row in set (0.00 sec) mysql> show create table test2; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test2 | CREATE TABLE `test2` ( `col1` text COLLATE utf8_unicode_ci, `col2` text COLLATE utf8_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci +-------+-----------------+ 1 row in set (0.00 sec) 

Now they both put the mapping in the show create table statement. Let me check schema information again.

 mysql> select table_schema, table_name, table_collation from information_schema.tables where table_schema = 'SO'; +--------------+------------+-----------------+ | table_schema | table_name | table_collation | +--------------+------------+-----------------+ | SO | test1 | utf8_unicode_ci | | SO | test2 | utf8_unicode_ci | +--------------+------------+-----------------+ 2 rows in set (0.00 sec) mysql> select table_schema, table_name, column_name, character_set_name, collation_name from information_schema.columns where table_schema = 'SO'; +--------------+------------+-------------+--------------------+-----------------+ | table_schema | table_name | column_name | character_set_name | collation_name | +--------------+------------+-------------+--------------------+-----------------+ | SO | test1 | col1 | utf8 | utf8_unicode_ci | | SO | test1 | col2 | utf8 | utf8_unicode_ci | | SO | test2 | col1 | utf8 | utf8_unicode_ci | | SO | test2 | col2 | utf8 | utf8_unicode_ci | +--------------+------------+-------------+--------------------+-----------------+ 4 rows in set (0.00 sec) 

Everything seems to be about the same. But what happens when we add an extra column to both tables?

 mysql> alter table test1 add column col3 text; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table test2 add column col3 text; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test1; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test1 | CREATE TABLE `test1` ( `col1` mediumtext COLLATE utf8_unicode_ci, `col2` mediumtext COLLATE utf8_unicode_ci, `col3` text COLLATE utf8_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci +-------+-----------------+ 1 row in set (0.00 sec) mysql> show create table test2; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test2 | CREATE TABLE `test2` ( `col1` text COLLATE utf8_unicode_ci, `col2` text COLLATE utf8_unicode_ci, `col3` text COLLATE utf8_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci +-------+-----------------+ 1 row in set (0.00 sec) 

In both cases, they collected the sort from the table. Therefore, there should not be much concern about the column added later due to the impact. Let check schema information again ...

 mysql> select table_schema, table_name, table_collation from information_schema.tables where table_schema = 'SO'; +--------------+------------+-----------------+ | table_schema | table_name | table_collation | +--------------+------------+-----------------+ | SO | test1 | utf8_unicode_ci | | SO | test2 | utf8_unicode_ci | +--------------+------------+-----------------+ 2 rows in set (0.00 sec) mysql> select table_schema, table_name, column_name, character_set_name, collation_name from information_schema.columns where table_schema = 'SO'; +--------------+------------+-------------+--------------------+-----------------+ | table_schema | table_name | column_name | character_set_name | collation_name | +--------------+------------+-------------+--------------------+-----------------+ | SO | test1 | col1 | utf8 | utf8_unicode_ci | | SO | test1 | col2 | utf8 | utf8_unicode_ci | | SO | test1 | col3 | utf8 | utf8_unicode_ci | | SO | test2 | col1 | utf8 | utf8_unicode_ci | | SO | test2 | col2 | utf8 | utf8_unicode_ci | | SO | test2 | col3 | utf8 | utf8_unicode_ci | +--------------+------------+-------------+--------------------+-----------------+ 6 rows in set (0.00 sec) 

Yes. Everything seems to work the same. But what about this hypothesis about it, only if it differs from MySQL by default, and not by default? Let set test1 go back to what it was.

 mysql> ALTER TABLE test1 CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test1; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test1 | CREATE TABLE `test1` ( `col1` mediumtext, `col2` mediumtext, `col3` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +-------+-----------------+ 1 row in set (0.00 sec) 

It seems we are looking as soon as we started. Now, to demonstrate that this is the default MySQL value, and not just the default database, set the default value for the database.

 mysql> Alter database SO default character set utf8 collate utf8_unicode_ci; Query OK, 1 row affected (0.00 sec) mysql> show create table test1; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test1 | CREATE TABLE `test1` ( `col1` mediumtext, `col2` mediumtext, `col3` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 +-------+-----------------+ 1 row in set (0.00 sec) mysql> show create table test2; +-------+-----------------+ | Table | Create Table +-------+-----------------+ | test2 | CREATE TABLE `test2` ( `col1` text COLLATE utf8_unicode_ci, `col2` text COLLATE utf8_unicode_ci, `col3` text COLLATE utf8_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci +-------+-----------------+ 1 row in set (0.00 sec) 

As you can see, test1 still looks like when we are just starting, and show create table not dependent on the default database.

Answer 2

I just did some testing and it seems to me that COLLATE is enabled (only in the mysqldump file) if it differs from setting the COLLATE table. Therefore, if you change the column and the table is the same, it will not be displayed in the mysqldump file.

With different settings

 CREATE TABLE `test` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `code` varchar(10) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin7; 

With the same settings (latin7)

 CREATE TABLE `test` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `code` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin7; 

These tests were performed in the same tables, just changing the sorting in the column.

Answer 3

If you redefine a character set in its value, but without specifying a sort, the sort will be deleted if no other mappings are created. You may need to clear the encoding and collation down to the table:

 select version(); +-----------+ | version() | +-----------+ | 5.6.17 | +-----------+ -- start with a table with collation on one column, charset on another SHOW CREATE TABLE mytable; CREATE TABLE `mytable` ( `address` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, `test` varchar(20) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- By changing charset and specifying no collation, this is cleared ALTER TABLE mytable MODIFY address varchar(150) CHARACTER SET utf8; CREATE TABLE `mytable` ( `address` varchar(150) CHARACTER SET utf8 DEFAULT NULL, `test` varchar(20) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- By specifying nothing we get no charset but default collation ALTER TABLE mytable MODIFY address varchar(150); CREATE TABLE `mytable` ( `address` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL, `test` varchar(20) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- changing all the fields at once does not help 

Success!

 -- So we clear the table AND the fields at the same time (a "change" which is no change at all, really) ALTER TABLE mytable CHARACTER SET utf8, MODIFY address varchar(150), MODIFY test varchar(20); SHOW CREATE TABLE mytable; CREATE TABLE `mytable` ( `address` varchar(150) DEFAULT NULL, `test` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 

I should also notice that I have server_character_set = utf8 and server_collate = utf8_unicode_ci in my.cnf (Linux OpenSuSE 13.2)