久久精品99无色码中文字幕,伊人久久综合成人网,亚洲第一极品精品无码久久

mysql多表join時報錯如下：[err]1267 – illegal mix of collations(utf8_general_ci,implicit) and (utf8_unicode_ci,implicit) for operation ‘=

就是說兩個表的排序規則（COLLATION）不同，無法完成比較。COLLATION是用在排序，大小比較上，一個字符集有一個或多種COLLATION，并且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二進制）結束。在做比較時，應該確保兩個表的字符排序相同。一般建表的時候不指定，可以走默認的，全是默認的就沒什么問題了。本文主要介紹了mysql表排序規則不同錯誤問題分析，希望能幫助到大家。

下面來模擬一下各種場景，表結構如下（utf8默認排序規則為utf8_general_ci）：

mysql&gt;?show?create?table?test.csG  ***************************?1.?row?***************************  ????Table:?cs  Create?Table:?CREATE?TABLE?`cs`?(  ?`id`?int(11)?DEFAULT?NULL,  ?`name`?varchar(10)?DEFAULT?NULL  )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8  1?row?in?set?(0.01?sec)

查看表默認排序規則集

mysql&gt;?select?TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION?from?information_schema.tables?where?table_name='cs';  +--------------+------------+-----------------+  |?TABLE_SCHEMA?|?TABLE_NAME?|?TABLE_COLLATION?|  +--------------+------------+-----------------+  |?test?????|?cs?????|?utf8_general_ci?|  +--------------+------------+-----------------+  1?row?in?set?(0.00?sec)

查看列排序規則集

mysql&gt;?select?TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME?from?information_schema.COLUMNS?where?TABLE_NAME='cs';  +--------------+------------+-------------+-----------------+  |?TABLE_SCHEMA?|?TABLE_NAME?|?COLUMN_NAME?|?COLLATION_NAME?|  +--------------+------------+-------------+-----------------+  |?test?????|?cs?????|?id?????|?NULL??????|  |?test?????|?cs?????|?name????|?utf8_general_ci?|  +--------------+------------+-------------+-----------------+  2?rows?in?set?(0.00?sec)

從utf8升級為utf8mb4是不支持online ddl的，如下：

mysql&gt;?ALTER?TABLE?cs?CONVERT?TO?CHARACTER?SET?utf8mb4,ALGORITHM=INPLACE,LOCK=NONE;  ERROR?1846?(0A000):?ALGORITHM=INPLACE?is?not?supported.?Reason:?Cannot?change?column?type?INPLACE.?Try?ALGORITHM=COPY.

從utf8.utf8_general_ci變更為utf8.utf8_unicode_ci是不支持online ddl的，如下：

mysql&gt;?ALTER?TABLE?cs?CONVERT?TO?CHARACTER?SET?utf8?collate?utf8_unicode_ci,ALGORITHM=INPLACE,LOCK=NONE;  ERROR?1846?(0A000):?ALGORITHM=INPLACE?is?not?supported.?Reason:?Cannot?change?column?type?INPLACE.?Try?ALGORITHM=COPY.

如果使用下面這種方式修改字符集，你會發現，只更改了表級的，沒有更改列級的。

mysql>?ALTER?TABLE?cs?CHARACTER?SET?utf8?collate?utf8_unicode_ci;??????  Query?OK,?0?rows?affected?(0.01?sec)  Records:?0?Duplicates:?0?Warnings:?0  ?  mysql>?select?TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION?from?information_schema.tables?where?table_name='cs';??????  +--------------+------------+-----------------+  |?TABLE_SCHEMA?|?TABLE_NAME?|?TABLE_COLLATION?|  +--------------+------------+-----------------+  |?test?????|?cs?????|?utf8_unicode_ci?|  +--------------+------------+-----------------+  1?row?in?set?(0.00?sec)  ?  mysql&gt;?select?TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME?from?information_schema.COLUMNS?where?TABLE_NAME='cs';  +--------------+------------+-------------+-----------------+  |?TABLE_SCHEMA?|?TABLE_NAME?|?COLUMN_NAME?|?COLLATION_NAME?|  +--------------+------------+-------------+-----------------+  |?test?????|?cs?????|?id?????|?NULL??????|  |?test?????|?cs?????|?name????|?utf8_general_ci?|  +--------------+------------+-------------+-----------------+  2?rows?in?set?(0.00?sec)

所以真正改字符集的時候別忘了加上CONVERT TO，如下：

mysql&gt;?ALTER?TABLE?cs?CONVERT?TO?CHARACTER?SET?utf8?collate?utf8_unicode_ci;  Query?OK,?5?rows?affected?(0.06?sec)  Records:?5?Duplicates:?0?Warnings:?0  ?  mysql&gt;?select?TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME?from?information_schema.COLUMNS?where?TABLE_NAME='cs';  +--------------+------------+-------------+-----------------+  |?TABLE_SCHEMA?|?TABLE_NAME?|?COLUMN_NAME?|?COLLATION_NAME?|  +--------------+------------+-------------+-----------------+  |?test?????|?cs?????|?id?????|?NULL??????|  |?test?????|?cs?????|?name????|?utf8_unicode_ci?|  +--------------+------------+-------------+-----------------+  2?rows?in?set?(0.00?sec)

要僅僅改變一個表的默認字符集，應使用此語句：

mysql&gt;?ALTER?TABLE?cs?default?CHARACTER?SET?utf8?collate?utf8_general_ci,ALGORITHM=INPLACE,LOCK=NONE;?????????  Query?OK,?0?rows?affected?(0.00?sec)  Records:?0?Duplicates:?0?Warnings:?0  ?  mysql&gt;?select?TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION?from?information_schema.tables?where?table_name='cs';??????  +--------------+------------+-----------------+  |?TABLE_SCHEMA?|?TABLE_NAME?|?TABLE_COLLATION?|  +--------------+------------+-----------------+  |?test?????|?cs?????|?utf8_general_ci?|  +--------------+------------+-----------------+  1?row?in?set?(0.00?sec)  ?  mysql&gt;?select?TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME?from?information_schema.COLUMNS?where?TABLE_NAME='cs';??  +--------------+------------+-------------+-----------------+  |?TABLE_SCHEMA?|?TABLE_NAME?|?COLUMN_NAME?|?COLLATION_NAME?|  +--------------+------------+-------------+-----------------+  |?test?????|?cs?????|?id?????|?NULL??????|  |?test?????|?cs?????|?name????|?utf8_unicode_ci?|  +--------------+------------+-------------+-----------------+  2?rows?in?set?(0.00?sec)

可以發現列字符集沒有改變，并且只有新的列才會默認繼承表的字符集（utf8.utf8_general_ci）。

MySQL表排序規則不同報錯問題分析