概念：COLLATE 含義 核對、校對

CREATE TABLE `table1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1',
`field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci COMMENT '測試表';

 mysql中字符型的列/字段 需要一個COLLATE類型來告知mysql如何對該列進行排序和比較

簡而言之，COLLATE會影響到ORDER BY語句的順序，會影響到WHERE條件中大于小于號篩選出來的結(jié)果，會影響**DISTINCT**、**GROUP BY**、**HAVING**語句的查詢結(jié)果。

mysql建索引的時候，如果索引列是字符類型，也會影響索引創(chuàng)建

凡是涉及到字符類型比較或排序的地方，都會和COLLATE有關(guān)系

COLLATE通常是和數(shù)據(jù)編碼（CHARSET）相關(guān)的，一般來說每種CHARSET都有多種它所支持的COLLATE，并且每種CHARSET都指定一種COLLATE為默認(rèn)值。例如Latin1編碼的默認(rèn)COLLATE為latin1_swedish_ci，GBK編碼的默認(rèn)COLLATE為gbk_chinese_ci，utf8mb4編碼的默認(rèn)值為utf8mb4_general_ci

 注意：建表時  DEFAULT CHARSET=utf8mb3 之前，現(xiàn)在多寫為 DEFAULT CHARSET=utf8mb4  （表情符號如:微信?—占據(jù)4bytes）

區(qū)分charset 與collate不同

charset設(shè)置字符串編碼集，常用的utf8，mysql遺留問題utf8最存儲3字節(jié)的大小，4字節(jié)的文字無法存儲，需要utf8mb4

collate和charset關(guān)聯(lián)，定義了字符串的排序規(guī)則，如utf8mb4_general_ci是和utf8mb4對應(yīng)的排序規(guī)則，ci為Case Insensitive，即大小寫不敏感

對應(yīng)cs為Case Sensitive，即大小寫敏感  【where name='A'與name='a'效果一致！ci不敏感時】

查看數(shù)據(jù)庫的所有charset和collate

SHOW CHARACTER SET;

SHOW COLLATION;

設(shè)置collate的級別【庫、表和字段】

庫    CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
表   CREATE TABLE tablename (

  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
 ...
 ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SQL級別查詢   顯示聲明覆蓋表中的COLLATE設(shè)置

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_general_ci;

優(yōu)先級順序是 SQL語句 > 列級別設(shè)置 > 表級別設(shè)置 > 庫級別設(shè)置 > 實例級別設(shè)置

額外對比-常用utf8mb4_general_ci

utf8mb4_general_ci : 不區(qū)分大小寫， utf8mb4_general_cs 區(qū)分大小寫

utf8mb4_bin : 將字符串每個字符串用二進制數(shù)據(jù)編譯存儲，區(qū)分大小寫，而且可以存二進制的內(nèi)容。

utf8mb4_unicode_ci : 校對規(guī)則僅部分支持Unicode校對規(guī)則算法,一些字符還是不能支持；utf8mb4_unicode_ci不能完全支持組合的記號。

項目遇到的問題

不同表同一字段，創(chuàng)建的數(shù)據(jù)庫表指定字符串排序規(guī)則為utf8mb4_general_ci和utf8mb4_unicode_ci兩種，導(dǎo)致關(guān)聯(lián)無法比較

【charset不同實際問題：慢SQL，查詢速度巨慢??！】

【collate不同實際問題：無法關(guān)聯(lián)，報錯??！】

-- 錯誤 SELECT a.mobile from play a INNER JOIN user_t b ON a.mobile = b.mobile

--正確 SELECT a.mobile from play a INNER JOIN user_t b ON a.mobile = b.mobile COLLATE utf8mb4_general_ci  [顯示轉(zhuǎn)為與a的collate規(guī)則一致！]

注意：一般情況下同數(shù)據(jù)庫中表的字段排序類型都是相同的！，除非開發(fā)者不小心導(dǎo)致

修改charset或collate

實際開發(fā)中，我建錯表的charset[uat與生成環(huán)境不同！按理需要一致的]

處理：將表中數(shù)據(jù)轉(zhuǎn)換；修改表的默認(rèn)字符集

修改庫的默認(rèn)字符集
alter database XXX default character set utf8mb4 COLLATE utf8mb4_unicode_ci;
修改表的默認(rèn)字符集
ALTER TABLE XXX DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
將表中原來的數(shù)據(jù)轉(zhuǎn)換為utf8mb4
ALTER TABLE XXX CONVERT TO CHARACTER SET utf8mb4;
修改列字符集
ALTER TABLE XXX CHANGE column_name VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

到此這篇關(guān)于深入MYSQL中的COLLATE的文章就介紹到這了,更多相關(guān)MYSQL COLLATE內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論