MySQL手记15 — 大小写问题
最近在测试时候,看到一个字符集校验规则的问题,由于设置了不同的校验规则,获取到的数据结果也就不一致。
MySQL的大小写规则,包含:表结构大小写、数据的大小写。结构的大小写在实例初始化的时候就需要进行指定:lower_case_table_names;数据的大小写,则由表或者字段的COLLATE进行指定。
一、表结构大小写
表结构大小写通常在实例初始化时候就行指定,lower_case_table_names,
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names
官方文档中的介绍:identifier-case-sensitivity
修改lower_case_table_names
(1)调整为区分大小写,会报错,因为该参数不可以进行动态调整:
(2)在实例初始化后修改my.cnf也是不能够启动成功的,错误信息:
[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server (‘0’) and data dictionary (‘1’).
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting
二、数据字符串大小写
对于数据中字符串的大小写是否做区分,主要是看指定的字符集:
字符集_ci结尾的排序规则,则是不区分大小写的,
_bin结尾,区分大小写
可以查看select * from INFORMATION_SCHEMA.COLLATIONS; 查看支持的字符集校对规则。
2.1 表级别COLLATIONS
两张表test1、test2
CREATE TABLE `test1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
CREATE TABLE `test2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
分别插入相同的数据:
mysql> insert into test1 values(1,'aaa');
Query OK, 1 row affected (0.05 sec)
mysql> insert into test2 values(1,'aaa');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test1 where name='AAA';
+----+------+
| id | name |
+----+------+
| 1 | aaa |
+----+------+
1 row in set (0.00 sec)
mysql> select * from test2 where name='AAA';
Empty set (0.00 sec)
mysql> select * from test2 where name='aaa';
+----+------+
| id | name |
+----+------+
| 1 | aaa |
+----+------+
1 row in set (0.00 sec)
在test1表中查询select * from test1 where name=’AAA’; 虽然是大写,但是由于表的collation为:utf8mb4_general_ci,所以是不区分大小写的,可以查询到结果。
在test2中执行相同的查询,查询不到结果,因为test2的collation是utf8mb4_bin,区分大小写,’AAA’不等于’aaa’,返回空。
2.2 字段级别
一个表test3,表的collate为utf8mb4_general_ci,字段name为utf8mb4_general_ci,addr为utf8mb4_bin:
CREATE TABLE `test3` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`addr` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
结果
对于不区分大小写的name为utf8mb4_general_ci:使用大写字符可以查询到结果
对于区分大小写的addr为utf8mb4_bin:使用大写字符查询不到结果
三、小结
在初始化MySQL实例、建表时候,需要注意到大小写的问题,并与开发人员沟通,若需要表结构大小写敏感,则调整lower_case_table_names;若需要数据的大小写敏感,调整utf8mb4_general_ci/utf8mb4_bin,当然常见的还有utf8_general_ci/utf8_bin。
欢迎关注公众号:朔的话:
ivermectin buy canada https://stromectoltrust.com/
ivermectin gel
ivermectin stromectol https://stromectoltrust.com/
how much ivermectin to give a goat
ivermectin for bird mites https://stromectoltrust.com/
ivermectin horse wormer paste
ivermectin withdrawal time https://stromectoltrust.com/
ivermectin for lyme
how long before ivermectin works https://stromectoltrust.com/
ivermectin warnings
ivermectin vs ivomec https://stromectoltrust.com/
ivermectin medication
scaly leg mite treatment ivermectin https://stromectoltrust.com/
ivermectin solution for birds
ivermectin dog https://stromectoltrust.com/
petsmart ivermectin
ivermectin dosage for demodectic mange in dogs https://stromectoltrust.com/
ivermectin for horses safe for humans
ivermectin pour on for goats https://stromectoltrust.com/
stromectol 3 mg tablet
amoxicillin without a doctor’s prescription https://pharmacyizi.com/
legal to buy prescription drugs from canada https://pharmacyizi.com/
fast ed meds online https://pharmacyizi.com/
sildenafil without a doctor’s prescription https://pharmacyizi.com/
carprofen without vet prescription https://pharmacyizi.com/
discount prescription drugs https://pharmacyizi.com/
buy prescription drugs online without https://pharmacyizi.com/
viagra without a doctor prescription walmart https://pharmacyizi.com/
ed meds online canada https://pharmacyizi.com/
buy anti biotics without prescription https://pharmacyizi.com/
canadian pharmacy no rx needed https://onlinepharmacy.men/
which online pharmacy is reliable recommended canadian pharmacies cheapest pharmacy
no prescription required pharmacy online pharmacy fungal nail cheapest prescription pharmacy
trusted online pharmacy mexican pharmacy canadian pharmacy 365
canadian pharmacies online pharmacy website india onlinepharmaciescanada com
web pharmacy online pharmacy pharmacy prices
my canadian pharmacy reviews reliable canadian pharmacy generic viagra online canadian pharmacy
tadalafil canadian pharmacy canada pharmacy not requiring prescription pharmacy online 365
online pharmacy products reliable canadian online pharmacy best pharmacy
canada pharmacy online real canadian pharmacy online pharmacy china
medical mall pharmacy buy online pharmacy uk express scripts pharmacy
online canadian drugstore https://canadiandrugs.best/
prescription drugs without doctor approval prescription drugs canada buy online best canadian pharmacy online
buy prescription drugs pain meds without written prescription best canadian pharmacy online
comfortis without vet prescription amoxicillin without a doctor’s prescription mexican pharmacy without prescription
prescription drugs without prior prescription prescription drugs online without mexican pharmacy without prescription
online canadian drugstore cheap pet meds without vet prescription ed meds online without doctor prescription
how to get prescription drugs without doctor carprofen without vet prescription anti fungal pills without prescription
buy prescription drugs canadian online pharmacy cvs prescription prices without insurance
canadian online drugs prescription drugs without prior prescription pet meds without vet prescription
mexican pharmacy without prescription ed meds online without doctor prescription canadian drug pharmacy
prescription meds without the prescriptions online canadian pharmacy buy prescription drugs without doctor
jeffers ivermectin: https://stromectolbestprice.com/
what is stromectol: https://stromectolbestprice.com/
stromectol buy online uk: https://stromectolbestprice.com/
ivermectin for pregnant goats: https://stromectolbestprice.com/
ivermectin fungal infection: https://stromectolbestprice.com/
how much ivermectin paste to give a dog: https://stromectolbestprice.com/
ivermectin oral for dogs: https://stromectolbestprice.com/
ivermectin cream before and after: https://stromectolbestprice.com/
ivermectin brand names: https://stromectolbestprice.com/
ivermectin for foxes: https://stromectolbestprice.com/
ivermectin fda label https://stromectolbestprice.com/
ivermectin equine ivermectin pour on for ringworm in cattle ivermectin for cutaneous larva migrans
tractor supply ivermectin peak prosperity ivermectin ivermectin sheep drench tractor supply
how fast does ivermectin work ivermectin for cows ivermectin for sale for humans
ivermectin 12 mg price ivermectin side effects dogs ivermectin paste dosage for dogs
ivermectin injectable for dogs liquid ivermectin for cats ivermectin dose for scabies in humans
ivermectin pill stromectol buy ivermectin chicken wormer
how to dose ivermectin for humans ivermectin for demodex mites in dogs ivermectin for goats lice
ivermectin, metronidazole ivermectin tablets for humans ivermectin pour on tractor supply
cattle ivermectin for humans where to get ivermectin ivermectin dogs
viagra without doctor prescription – https://drugsbestprice.com/
amoxicillin without a doctor’s prescription – https://drugsbestprice.com/
comfortis for dogs without vet prescription – https://drugsbestprice.com/
buy anti biotics without prescription – https://drugsbestprice.com/
pain meds without written prescription – https://drugsbestprice.com/
prescription drugs without doctor approval – https://drugsbestprice.com/
ed meds online canada – https://drugsbestprice.com/
cheap pet meds without vet prescription – https://drugsbestprice.com/
viagra without a prescription – https://drugsbestprice.com/
prescription drugs online without doctor – https://drugsbestprice.com/
viagra without a doctor prescription: discount prescription drugs cialis without a doctor’s prescription
pet meds without vet prescription canada prescription drugs online without doctor – buy prescription drugs without doctor
canada ed drugs tadalafil without a doctor’s prescription – buy cheap prescription drugs online
non prescription ed drugs best canadian pharmacy online – mexican pharmacy without prescription
cat antibiotics without pet prescription pain meds online without doctor prescription – online canadian drugstore
dog antibiotics without vet prescription online canadian drugstore – canadian pharmacy online
non prescription ed drugs pain meds without written prescription – discount prescription drugs
prescription drugs online ed meds online without doctor prescription – canadian drug pharmacy
best canadian pharmacy online prescription drugs – buy prescription drugs from india
sildenafil without a doctor’s prescription prescription drugs online without doctor – comfortis without vet prescription