Bagaimana untuk membezakan antara huruf besar dan huruf kecil apabila menanyakan data yang disimpan dalam MySQL

Penerangan senario

Selepas menyegerakkan jadual Hive ke MySQL hari ini, salah satu lajur ialah satu-satunya lajur, tetapi apabila membuat pertanyaan dalam MySQLcount nilai yang ditanya oleh distinct count adalah berbeza Dari sudut pandangan ini, terdapat data pendua (yang tidak sepatutnya berlaku, kerana dalam Hive, kedua-dua nilai adalah sama), kemudian ketahui pendua. data Selepas melihatnya, saya mendapati bahawa ia adalah masalah kes Kemudian saya menyemaknya dan mendapati bahawa secara lalai dalam pangkalan data MySQL, semua operasi yang berkaitan pada medan rentetan adalah "tidak sensitif huruf".

Ini berbeza daripada pangkalan data popular yang lain.

Penyelesaian

1. Tentukan sensitiviti huruf besar semasa membuat pertanyaan

MySQL membenarkan anda menentukan kepekaan huruf besar-besaran semasa membuat pertanyaan Anda perlu menggunakan kata kunci BINARY untuk membuat pertanyaan Seperti berikut:

SELECT * FROM student WHERE BINARY name = 'ZhangSan';
--或者
SELECT * FROM student WHERE name = BINARY 'ZhangSan';

Banyak kali apabila masalah yang dinyatakan di atas ditemui dalam pangkalan data MySQL, sistem telah berjalan untuk satu tempoh masa, dan kos menggunakan kaedah dua atau tiga mungkin sangat tinggi.

Kelebihan terbesar menggunakan kaedah ini ialah fungsi boleh dilaksanakan dengan cepat.

Walau bagaimanapun, kaedah ini juga mempunyai had yang besar: ia boleh menyebabkan prestasi pertanyaan menurun kerana indeks tidak boleh digunakan.

Alasannya mudah difahami, kerana indeks untuk medan pertanyaan juga ditetapkan dalam cara yang tidak peka huruf besar-besaran.

Melainkan jumlah data tidak besar, atau anda tidak mengambil berat tentang kehilangan prestasi dalam aplikasi anda, anda hanya boleh memilih kaedah 2 atau 3.

2. Nyatakan sensitiviti huruf besar medan semasa mentakrifkan struktur jadual

Nyatakan kepekaan huruf besar medan tertentu semasa mencipta jadual, contohnya adalah seperti berikut:

CREATE TABLE student (
  ...
  name VARCHAR(64) BINARY NOT NULL,
  ...

)

Kata kunciBINARY Medan nama yang ditentukan adalah peka huruf besar-kecil.

Dengan cara ini, walaupun kata kunci BINARY tidak digunakan dalam pertanyaan, pernyataan pertanyaan adalah sensitif huruf besar-besaran.

Indeks berkaitan nama yang dibuat atas dasar ini juga sensitif huruf besar-besaran, jadi indeks boleh digunakan untuk meningkatkan prestasi.

MySQL membenarkan penggunaan kata kunci BINARY pada kebanyakan jenis rentetan untuk menunjukkan bahawa semua operasi pada medan ini adalah sensitif huruf besar-kecil Untuk maklumat lanjut, sila lihat dokumentasi MySQL rasmi.

Pendekatan ini membolehkan pereka bentuk mengawal dengan tepat sama ada setiap medan adalah sensitif huruf besar-besaran. Dalam reka bentuk banyak sistem, selalunya dijangka bahawa semua medan, atau bahkan kebanyakan medan, adalah sensitif huruf besar-kecil. MySQL juga menyediakan penyelesaian, yang memerlukan penggunaan kaedah tiga.

3. Ubah suai peraturan penyusunan (COLLATE)

Jalankan perintah show create table <tablename></tablename> dalam MySQL, anda boleh melihat pernyataan penciptaan jadual bagi jadual, contohnya seperti berikut:

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

Kita boleh memahami kebanyakan medan, tetapi apa yang kita akan lihat hari ini ialah kata kunci COLLATE. Apakah maksud utf8_general_ci yang sepadan dengan nilai ini? Mari ketahui di bawah.

Untuk apa COLLATE digunakan?

Mereka yang dibangunkan menggunakan Navicat mungkin kelihatan biasa, kerana jawapan telah diberikan dalam pilihan:

Apa yang dipanggil utf8_general_ci sebenarnya digunakan untuk Peraturan menyusun. 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 mempengaruhi susunan PENYATAAN ORDER BY, ia akan menjejaskan hasil yang ditapis oleh tanda yang lebih besar daripada dan kurang dalam keadaan WHERE, dan ia akan menjejaskan penciptaan indeks, tetapi kami tidak dapat melihat kesan ini. Ringkasnya, mana-mana sahaja yang melibatkan perbandingan atau pengisihan jenis aksara akan dikaitkan dengan COLLATE. Inti pelbagai operasi yang melibatkan rentetan mesti melibatkan peraturan pengisihan aksara (COLLATE, juga diterjemahkan sebagai "menyemak"). Sama ada operasi rentetan MySQL adalah sensitif huruf besar pada dasarnya bergantung pada pengumpulan COLLATE yang digunakannya. ialah nilai COLLATE khusus. Setiap COLLATE tertentu sepadan dengan set aksara yang unik Dapat dilihat bahawa set aksara yang sepadan dengan COLLATE ini ialah . Berkaitan dengan isu sensitiviti huruf besar adalah akhirannya

, yang dijelaskan oleh dokumentasi rasmi MySQL ialah singkatan

, yang bermaksud ia tidak sensitif huruf besar-besaran. Memandangkan MySQL menentukan

sebagai COLLATE lalai bagi set aksara utf8, ini juga membawa kepada fenomena yang disebut pada permulaan artikel. Pada masa yang sama, MySQL juga menyediakan pilihan nilai COLLATE yang lain,

adalah sensitif huruf besar-besaran. Malah, semua COLLATE sensitif huruf besar-besaran diakhiri dengan utf8_general_ci atau utf8, yang pertama ialah singkatan _ci dan yang kedua ialah singkatan Case Ignore. utf8_general_ci

Perbezaan antara pelbagai COLLATE

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

Sebagai nota sampingan, terdapat dua pengekodan dalam MySQL: utf8 dan utf8mb4 Sila lupakan utf8 dalam MySQL dan sentiasa gunakan utf8mb4. Ini adalah isu warisan MySQL utf8 dalam MySQL hanya boleh menyokong pengekodan aksara sehingga 3bait panjang Untuk beberapa teks yang perlu menduduki 4bait, utf8 MySQL tidak menyokongnya.

Ramai COLLATE mempunyai perkataan _ci, iaitu singkatan daripada Case Insensitive, iaitu, bebas kes, iaitu "A" dan "a" ialah digunakan dalam pengisihan dan perbandingan Semua masa adalah sama. selection * from table1 where field1="a" Anda juga boleh memilih nilai medan1 sebagai "A". Pada masa yang sama, bagi COLLATE yang mempunyai akhiran _cs, ia adalah Case Sensitive, yang sensitif huruf besar-besaran.

Gunakan perintah 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.

Dalam gambar kita dapat melihat peraturan pengisihan 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:

Kaedah perbandingan UTF8mb4_bin adalah untuk merawat semua aksara sebagai rentetan binari dan kemudian membandingkannya dari 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. Tetapi untuk huruf di beberapa negara Barat, utf8mb4_unicode_ci lebih sesuai dengan tabiat bahasa mereka daripada utf8mb4_general_ci General ialah piawaian MySQL yang lebih lama. Sebagai contoh, huruf Jerman "ß" bersamaan dengan dua huruf "ss" dalam utf8mb4_unicode_ci (ini selaras dengan amalan Jerman), tetapi dalam utf8mb4_general_ci, ia bersamaan dengan huruf "s". Walau bagaimanapun, perbezaan halus antara kedua-dua pengekodan 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), ai bermaksud aksen tidak sensitif (sebutan tidak relevan), seperti e, è, é, ê ; dilayan sama rata. Pautan rujukan berkaitan 1, pautan rujukan berkaitan 2

KOLLAT tahap tetapan dan keutamaannya

Pangkalan data MySQL membenarkan dalam perpustakaan, jadual dan Lajur Nyatakan Pengumpulan pada tiga peringkat. Apabila dinyatakan bersama, hubungan keutamaan ialah: lajur >

KOLLAT boleh ditetapkan pada peringkat contoh, peringkat perpustakaan, peringkat jadual, peringkat lajur dan SQL menentukan . Apabila dinyatakan secara serentak, hubungan keutamaan ialah: lajur spesifikasi SQL >

• Tetapan COLLATE peringkat contoh ialah pembolehubah sistem collation_connection dalam fail konfigurasi MySQL atau arahan permulaan.

• Pernyataan untuk menetapkan COLLATE di peringkat perpustakaan adalah seperti berikut:

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

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

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

CREATE TABLE table_name (
……
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT = '表注释';

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

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

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

如果列级别没有设置 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 和表上设置的一样。

Atas ialah kandungan terperinci Bagaimana untuk membezakan antara huruf besar dan huruf kecil apabila menanyakan data yang disimpan dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!