您好,登錄后才能下訂單哦!
MySQL表排序規(guī)則不同報錯是什么問題導致的?很多人都不太了解,今天小編為了讓大家更加了解MySQL表排序規(guī)則,所以給大家總結了以下內容,一起往下看吧。
MySQL多表join時報錯如下:[Err]1267 – Illegal mix of collations(utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=
就是說兩個表的排序規(guī)則(COLLATION)不同,無法完成比較。COLLATION是用在排序,大小比較上,一個字符集有一個或多種COLLATION,并且以_ci(大小寫不敏感)、_cs(大小寫敏感)或_bin(二進制)結束。在做比較時,應該確保兩個表的字符排序相同。一般建表的時候不指定,可以走默認的,全是默認的就沒什么問題了。本文主要介紹了MySQL表排序規(guī)則不同錯誤問題分析,希望能幫助到大家。
下面來模擬一下各種場景,表結構如下(utf8默認排序規(guī)則為utf8_general_ci):
mysql> show create table test.cs\G *************************** 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)
查看表默認排序規(guī)則集
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_general_ci | +--------------+------------+-----------------+ 1 row in set (0.00 sec)
查看列排序規(guī)則集
mysql> 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> 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> 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.
如果使用下面這種方式修改字符集,你會發(fā)現(xiàn),只更改了表級的,沒有更改列級的。
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> 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> 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> 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> 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> 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> 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)
可以發(fā)現(xiàn)列字符集沒有改變,并且只有新的列才會默認繼承表的字符集(utf8.utf8_general_ci)。
以上就是MySQL表排序規(guī)則不同報錯是什么問題導致的簡略介紹,當然詳細使用上面的不同還得要大家自己使用過才領會。如果想了解更多,歡迎關注億速云行業(yè)資訊頻道哦!
億速云在今年春節(jié)期間,新上線了三款“云數(shù)據(jù)庫”的產品和服務 :云數(shù)據(jù)庫MySQL、云數(shù)據(jù)庫Redis以及云數(shù)據(jù)庫Memcached。其中,云數(shù)據(jù)庫MySQL是一種即開即用、穩(wěn)定可靠、可彈性伸縮的“在線數(shù)據(jù)庫服務”,具有多重安全防護措施和完善的性能監(jiān)控體系,并提供專業(yè)的數(shù)據(jù)庫備份、恢復及優(yōu)化方案,使企業(yè)與用戶能夠更加專注于應用開發(fā)和業(yè)務發(fā)展。
免責聲明:本站發(fā)布的內容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經查實,將立刻刪除涉嫌侵權內容。