将列排序转换为表/数据库默认值

Every single post on SO that I have seen to accomplish this suggests running the following SQL:

我看到的每一篇文章都建议运行以下SQL:

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

The problem with this, unless I am mistaken, is that it explicitly specifies the column collations, so you end up with something like this when you mysqldump the database:

问题是，除非我弄错了，它显式地指定列排序，所以当mysqldump数据库时，您会得到这样的结果:

  `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 the column collations to the table or database default 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,

What I want, is to convert all columns to utf8_unicode_ci (the table/database default), but not have each column explicitly set to that collation, so that when I mysqldump the converted table, it just looks like this:

我想要的是，将所有列转换为utf8_unicode_ci(表/数据库默认值)，但不要将每个列显式地设置为该排序规则，这样当我mysqldump转换后的表时，它看起来就像这样:

  `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 table creation statement that defines the default character set and collation: ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

表创建语句末尾的一行定义了默认字符集和排序:ENGINE=InnoDB默认字符集=utf8 COLLATE=utf8_unicode_ci

3 个解决方案

#1

If your table or column is different from the MySQL default, in my case latin1_sweedish_ci, then it will print out the collation with the column. See the following experimentation that demonstrates this.

如果表或列与MySQL默认值不同，在我的例子中是latin1_sweedish_ci，那么它将打印出与列的排序。请参见下面演示这一点的实验。

To set the default character set, see this post.

要设置默认字符集，请参见本文。

First, lets create a datbase with two tables. One table has the character set and collation specified.

首先，让我们用两个表创建一个datbase。一个表具有指定的字符集和排序规则。

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 the show create table to see what it looks like:

现在检查一下show create table，看看它是什么样子:

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 see that test2 already looks like the columns are specified specifically rather than using the default. I suspect if it's different from the MySQL default it will list it rather than if it's different from the table default. Let's now see how they look in the information_schema database.

我们看到test2看起来已经指定了特定的列，而不是使用默认值。我怀疑它是否与MySQL默认值不同，它会列出它，而不是与表默认值不同。现在让我们看看它们在information_schema数据库中的样子。

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 looks like the columns have a specific character set and collation regardless of if we specified it. Lets update test1 to the prefered character set and collation and see what happens.

看起来这些列具有特定的字符集和排序，而不管我们是否指定它。让我们将test1更新为首选的字符集和排序，看看会发生什么。

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're both putting the collation in the show create table statement. Let's check the information_schema again.

现在它们都把排序规则放到了show create table语句中。让我们再次检查information_schema。

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)

Looks to be all 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 picked up the collation from the table. So there shouldn't be much worry about a column added later being out of whack. Let's check the information_schema one more time...

在这两种情况下，他们都从桌子上拿起排序表。因此，不应该太担心以后添加的专栏出现问题。让我们再检查一下information_schema……

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)

Yeah. All looks like it's working the same way. But what about that hypothesis about it only displaying if it is different from the MySQL default as opposed to the table default? Let's set test1 back to what it used to be.

是的。看起来都是一样的。但如果它只显示与MySQL默认值不同而与表默认值不同的情况，那这个假设又如何呢?让我们将test1设置回原来的状态。

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)

Seems to look just like when we started. Now to deomonstrate that it is the MySQL default and not just the database default, let's set the default for the database.

看起来就像我们刚开始的时候。现在，要排除它是MySQL默认值，而不仅仅是数据库默认值，让我们为数据库设置默认值。

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 is still looking like when we first started and the show create table is not affected by the database default.

如您所见，test1仍然与我们第一次启动时类似，并且show create表不受数据库默认值的影响。

#2

I have just done some testing and it appears to me that the COLLATE is only included (in the mysqldump file) if it is different from the table COLLATE setting. So if you change the column and the table to be the same then it will not be output in the mysqldump file.

我刚刚做了一些测试，在我看来，如果排序规则与表排序规则设置不同，则只包含排序规则(在mysqldump文件中)。因此，如果您更改了列和表，那么它将不会在mysqldump文件中输出。

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)

设置相同(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 done on the same tables, just by changing the collation on the column.

这些测试是在相同的表上完成的，只是通过更改列上的排序规则。

#3

If you redefine the character set to its value but without specifying collation, the collation is cleared, unless there are other collations in effect. You may have to clear charset and collation all the way up 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 observe that I have server_character_set = utf8 and server_collate = utf8_unicode_ci in my my.cnf (Linux OpenSuSE 13.2)

我还应该注意，我的my.cnf中有server_character_set = utf8和server_collate = utf8_unicode_ci (Linux OpenSuSE 13.2)