Penjelasan terperinci tentang peranan COLLATE dalam MYSQL dan perbezaan antara pelbagai COLLATE

Apakah itu COLLATE dalam MYSQL?

Laksanakan arahan cipta jadual dalam mysql, dan anda boleh melihat penyataan penciptaan jadual bagi sesuatu jadual adalah seperti berikut:

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;

Kami mempunyai kebanyakan bidang yang saya boleh fahami, tetapi apa yang saya ingin bincangkan hari ini ialah kata kunci COLLATE. Apakah maksud utf8_unicode_ci yang sepadan di sebalik nilai ini? Jika anda menggunakan soalan ini untuk mengambil peperiksaan DBA semasa temu duga, soalan itu sepatutnya boleh membingungkan kebanyakan orang.

Untuk apa COLLATE digunakan?

Pembangunan menggunakan phpmyadmin mungkin kelihatan sangat biasa, kerana pengepala Cina telah memberikan jawapan:

tangkapan skrin phpmyadmin

Apa yang dipanggil utf8_unicode_ci sebenarnya adalah peraturan yang digunakan untuk mengisih. Untuk lajur jenis aksara dalam mysql, seperti lajur jenis VARCHAR, CHAR dan TEXT, jenis COLLATE diperlukan untuk memberitahu mysql cara mengisih dan membandingkan lajur. Ringkasnya, COLLATE akan menjejaskan susunan penyata ORDER BY, hasil yang ditapis oleh tanda yang lebih besar daripada atau kurang dalam keadaan WHERE, dan **DISTINCT**, **GROUP BY** dan **HAVING* * keputusan pertanyaan. Di samping itu, apabila MySQL membina indeks, jika lajur indeks adalah jenis aksara, ia juga akan menjejaskan penciptaan indeks, tetapi kita tidak dapat melihat kesan ini. Pendek kata, di mana sahaja perbandingan atau pengisihan jenis aksara terlibat, ia akan dikaitkan dengan COLLATE.

Perbezaan antara pelbagai COLLATE

COLLATE biasanya berkaitan dengan pengekodan data (CHARSET Secara umumnya, setiap CHARSET mempunyai berbilang COLLATE yang disokong dan setiap CHARSET). COLLATE sebagai nilai lalai. Sebagai contoh, COLLATE lalai untuk pengekodan Latin1 ialah latin1_swedish_ci, COLLATE lalai untuk pengekodan GBK ialah gbk_chinese_ci dan nilai lalai untuk pengekodan utf8mb4 ialah utf8mb4_general_ci.

Sebagai nota sampingan, terdapat dua pengekodan dalam mysql: utf8 dan utf8mb4 Dalam mysql, sila lupakan **utf8** dan sentiasa gunakan **utf8mb4**. Ini adalah isu lama MySQL UTF8 dalam MySQL hanya boleh menyokong pengekodan aksara dengan panjang maksimum 3 bait Untuk beberapa teks yang perlu menduduki 4 bait, UTF8 MySQL tidak menyokongnya.

Banyak COLLATE mempunyai perkataan _ci, yang merupakan singkatan daripada Case Insensitive, yang bermaksud bahawa "A" dan "a" dilayan sama rata apabila mengisih dan membandingkan. selection * dari table1 di mana field1="a" juga boleh memilih nilai field1 sebagai "A". Pada masa yang sama, bagi mereka COLLATE dengan akhiran _cs, ia adalah Sensitif Huruf, iaitu sensitif huruf besar.

Gunakan arahan show collation dalam mysql untuk melihat semua COLLATE yang disokong oleh mysql. Mengambil utf8mb4 sebagai contoh, semua COLLATE yang disokong oleh pengekodan ini adalah seperti yang ditunjukkan dalam rajah di bawah.

Semua COLLATE yang berkaitan dengan utf8mb4 dalam mysql

Dalam gambar kita dapat melihat peraturan penyusunan bahasa di banyak negara. Tiga yang biasa digunakan di China ialah utf8mb4_general_ci (lalai), utf8mb4_unicode_ci dan utf8mb4_bin. Mari kita terokai perbezaan antara ketiga-tiga ini:

Pertama sekali, kaedah perbandingan utf8mb4_bin adalah untuk merawat secara langsung semua aksara sebagai rentetan binari, dan kemudian membandingkannya daripada bit tertinggi kepada bit terendah. Jadi jelas ia sensitif huruf besar.

Sebenarnya tiada perbezaan antara utf8mb4_unicode_ci dan utf8mb4_general_ci untuk bahasa Cina dan Inggeris. Untuk sistem yang kami bangunkan untuk kegunaan domestik, anda boleh memilih mana-mana satu. Cuma untuk huruf di beberapa negara Barat, utf8mb4_unicode_ci lebih sesuai dengan tabiat bahasa mereka daripada utf8mb4_general_ci ialah standard MySQL yang lebih lama. Sebagai contoh, huruf Jerman "ß" adalah bersamaan dengan dua huruf "ss" dalam utf8mb4_unicode_ci (ini selaras dengan tabiat Jerman), tetapi dalam utf8mb4_general_ci, ia bersamaan dengan huruf "s". Walau bagaimanapun, perbezaan halus antara kedua-dua pengekodan adalah sukar untuk dilihat untuk perkembangan normal. Kami jarang menggunakan medan teks untuk mengisih secara langsung Untuk mengambil langkah ke belakang, walaupun satu atau dua huruf tidak sejajar, bolehkah ia benar-benar membawa akibat bencana kepada sistem? Berdasarkan pelbagai siaran dan perbincangan yang terdapat di Internet, lebih ramai orang mengesyorkan menggunakan utf8mb4_unicode_ci, tetapi mereka tidak begitu tahan terhadap sistem yang menggunakan nilai lalai, dan tidak fikir terdapat sebarang masalah besar. Kesimpulan: Adalah disyorkan untuk menggunakan utf8mb4_unicode_ci Untuk sistem yang sudah menggunakan utf8mb4_general_ci, tidak perlu menghabiskan masa untuk mengubah suai.

Perkara lain yang perlu diambil perhatian ialah bermula dari mysql 8.0, CHARSET lalai mysql bukan lagi Latin1, tetapi telah ditukar kepada utf8mb4 (pautan rujukan), dan COLLATE lalai juga telah ditukar kepada utf8mb4_0900_ai_ci. utf8mb4_0900_ai_ci secara amnya ialah subbahagian lanjut bagi unicode 0900 merujuk kepada bilangan algoritma perbandingan unikod (versi Algoritma Pengumpulan Unikod), dan ai bermaksud aksen tidak sensitif (sebutan tidak relevan Contohnya, e, è, é, ê dan ë dilayan sama rata. Pautan rujukan berkaitan 1, pautan rujukan berkaitan 2

KOLLAT tahap tetapan dan keutamaannya

设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。

库级别设置COLLATE的语句如下：

CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果库级别没有设置CHARSET和COLLATE，则库级别默认的CHARSET和COLLATE使用实例级别的设置。在mysql8.0以下版本中，你如果什么都不修改，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

表级别的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：

CREATE TABLE (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果表级别没有设置CHARSET和COLLATE，则表级别会继承库级别的CHARSET与COLLATE。

列级别的设置，则在CREATE TABLE中声明列的时候指定，例如

CREATE TABLE (
`field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
……
) ……

如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。

最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置，不太常用，了解即可：

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

如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。也就是说列上所指定的COLLATE可以覆盖表上指定的COLLATE，表上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和表上设置的一样。

以上就是关于mysql的COLLATE相关知识。不过，在系统设计中，我们还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。

推荐学习：《mysql视频教程》

Atas ialah kandungan terperinci Penjelasan terperinci tentang peranan COLLATE dalam MYSQL dan perbezaan antara pelbagai COLLATE. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!