将列排序规则转换为表/数据库默认值 [英] Convert column collation to table/database default
本文介绍了将列排序规则转换为表/数据库默认值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我已经看到要完成这一切的每一个帖子建议运行以下SQL：
ALTER TABLE< tablename> ; CONVERT TO CHARACTER SET utf8_unicode_ci;
除非我错了，问题是它明确指定列的排序规则，当你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，
我的问题是..是没有办法将列归类转换为表或数据库默认而不这样做？ / p>
例如，我的表格可能如下所示：
`address` varchar（150）DEFAULT NULL，
`city` varchar（100）DEFAULT NULL，
`state` varchar（2）COLLATE utf8_general_ci DEFAULT NULL，
`zipcode` varchar ）COLLATE utf8_unicode_ci DEFAULT NULL，
我想要的是将所有列转换为 utf8_unicode_ci （表/数据库默认），但没有明确设置每个列的排序规则，所以当我mysqldump转换表，它只是这样：
`address` varchar（150）DEFAULT NULL，
`city` varchar（100）DEFAULT NULL，
`state` varchar 2）DEFAULT NULL，
`zipcode` varchar（10）DEFAULT NULL，
在表创建语句结束时定义默认字符集和排序规则： ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci
解决方案
如果你的表或列不同于MySQL默认值，在我的例子中是latin1_sweedish_ci，那么它将打印出与列的排序规则。请参阅以下演示此操作的实验。
要设置默认字符集，请参阅此帖。
首先，两个表。一个表具有指定的字符集和排序规则。
mysql>创建数据库SO;
mysql>使用SO;
mysql>创建表test1（col1文本，col2文本）;
mysql>创建表test2（col1文本，col2文本）字符集utf8 collate utf8_unicode_ci;
现在检查 show create table 它看起来像：
mysql> show create table test1;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| test1 | CREATE TABLE`test1`（
`col1` text，
`col2` text
）ENGINE = InnoDB DEFAULT CHARSET = latin1
+ ------- + --------------- +
集合中的1行（0.00秒）
mysql> show create table test2;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| 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行（0.00秒）
我们看到 test2 已经看起来像专门指定的列，而不是使用默认值。我怀疑如果它不同于MySQL默认值，它会列出它，而不是如果它不同于表默认。现在让我们看看他们在information_schema数据库中的样子。
mysql>选择table_schema，table_name，table_collation从information_schema.tables其中table_schema ='SO';
+ -------------- + ------------ + ----------------- - +
| table_schema | table_name | table_collation |
+ -------------- + ------------ + ----------------- - +
| SO | test1 | latin1_swedish_ci |
| SO | test2 | utf8_unicode_ci |
+ -------------- + ------------ + ----------------- - +
集合中的2行（0.00秒）
mysql>选择table_schema，table_name，column_name，character_set_name，collation_name从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 |
| S0 test2 | col2 | utf8 | utf8_unicode_ci |
+ -------------- + ------------ + ------------- + --- ----------------- + ------------------- +
集合中的4行（0.00秒）
看起来像列有一个特定的字符集和排序规则，让更新test1到首选的字符集和排序，看看会发生什么。
mysql> ALTER TABLE test1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
查询OK，0行受影响（0.05秒）
记录：0重复：0警告：0
mysql> show create table test1;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| 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行（0.00秒）
mysql> show create table test2;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| 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行（0.00秒）
现在，他们都将排序规则放在 show create table 语句中。让我们再次检查一下information_schema。
mysql>选择table_schema，table_name，table_collation从information_schema.tables其中table_schema ='SO';
+ -------------- + ------------ + ----------------- +
| table_schema | table_name | table_collation |
+ -------------- + ------------ + ----------------- +
| SO | test1 | utf8_unicode_ci |
| SO | test2 | utf8_unicode_ci |
+ -------------- + ------------ + ----------------- +
集合中的2行（0.00秒）
mysql>选择table_schema，table_name，column_name，character_set_name，collation_name从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 |测试2 | col1 | utf8 | utf8_unicode_ci |
| SO | test2 | col2 | utf8 | utf8_unicode_ci |
+ -------------- + ------------ + ------------- + --- ----------------- + ----------------- +
集合中的4行（0.00秒）
看起来都是一样的。但是，当我们为这两个表添加一个额外的列时会发生什么？
mysql> alter table test1 add column col3 text;
查询OK，0行受影响（0.05秒）
记录：0重复：0警告：0
mysql> alter table test2 add column col3 text;
查询OK，0行受影响（0.06秒）
记录：0重复：0警告：0
mysql> show create table test1;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| 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行（0.00秒）
mysql> show create table test2;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| 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行（0.00秒）
在这两种情况下，他们从表中选择了排序规则。所以不应该担心一个列后来添加的列。让我们再次检查一下information_schema ...
mysql>选择table_schema，table_name，table_collation从information_schema.tables其中table_schema ='SO';
+ -------------- + ------------ + ----------------- +
| table_schema | table_name | table_collation |
+ -------------- + ------------ + ----------------- +
| SO | test1 | utf8_unicode_ci |
| SO | test2 | utf8_unicode_ci |
+ -------------- + ------------ + ----------------- +
集合中的2行（0.00秒）
mysql>选择table_schema，table_name，column_name，character_set_name，collation_name从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行（0.00秒）
是的。所有看起来都像相同的方式工作。但是那个假设只是显示如果它不同于MySQL默认，而不是表默认值呢？让我们将 test1 恢复到以前的状态。
mysql> ALTER TABLE test1 CONVERT TO CHARACTER set latin1 COLLATE latin1_swedish_ci;
查询OK，0行受影响（0.02秒）
记录：0重复：0警告：0
mysql> show create table test1;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| test1 | CREATE TABLE`test1`（
`col1` mediumtext，
`col2` mediumtext，
`col3` text
）ENGINE = InnoDB DEFAULT CHARSET = latin1
+ ------ + ----------------- +
集合中的1行（0.00秒）
看起来就像我们开始的时候。现在要说明它是MySQL默认值，而不是数据库默认值，让我们设置数据库的默认值。
mysql>更改数据库SO默认字符集utf8 collate utf8_unicode_ci;
查询OK，1行受影响（0.00秒）
mysql> show create table test1;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| test1 | CREATE TABLE`test1`（
`col1` mediumtext，
`col2` mediumtext，
`col3` text
）ENGINE = InnoDB DEFAULT CHARSET = latin1
+ ------ + ----------------- +
集合中的1行（0.00秒）
mysql> show create table test2;
+ ------- + ----------------- +
|表|创建表
+ ------- + ----------------- +
| 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行（0.00秒）
可以看到，test1仍然看起来像我们第一次启动时， show create table 不受数据库默认值的影响。
Every single post on SO that I have seen to accomplish this suggests running the following 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:
`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:
`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
解决方案
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.
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.
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:
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.
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.
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.
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...
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> 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> 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.
这篇关于将列排序规则转换为表/数据库默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
