首页 » 猿-技术 » 正文

MySQL手记15 — 大小写问题

发表于: CoderCoder.cn · 作者: ·  2020-4-29 ·  726 views  ·  82 replies 
本站文章均为原创,转载请注明出处和链接!

      最近在测试时候,看到一个字符集校验规则的问题,由于设置了不同的校验规则,获取到的数据结果也就不一致。
      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_ciaddr为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。

欢迎关注公众号:朔的话

本文链接: http://codercoder.cn/index.php/2020/04/mysql-note-15-case-sensitivity-and-case-insensitivity/
«上一篇: :下一篇»

评论区

  1. Stromectol发表于:2022-07-13 18:18

    ivermectin buy canada https://stromectoltrust.com/
    ivermectin gel

  2. Stromectol发表于:2022-07-13 19:35

    ivermectin stromectol https://stromectoltrust.com/
    how much ivermectin to give a goat

  3. Stromectol发表于:2022-07-13 20:48

    ivermectin for bird mites https://stromectoltrust.com/
    ivermectin horse wormer paste

  4. Stromectol发表于:2022-07-13 22:01

    ivermectin withdrawal time https://stromectoltrust.com/
    ivermectin for lyme

  5. Stromectol发表于:2022-07-13 23:16

    how long before ivermectin works https://stromectoltrust.com/
    ivermectin warnings

  6. Stromectol发表于:2022-07-14 00:31

    ivermectin vs ivomec https://stromectoltrust.com/
    ivermectin medication

  7. Stromectol发表于:2022-07-14 01:48

    scaly leg mite treatment ivermectin https://stromectoltrust.com/
    ivermectin solution for birds

  8. Stromectol发表于:2022-07-14 03:00

    ivermectin dog https://stromectoltrust.com/
    petsmart ivermectin

  9. Stromectol发表于:2022-07-14 04:20

    ivermectin dosage for demodectic mange in dogs https://stromectoltrust.com/
    ivermectin for horses safe for humans

  10. Stromectol发表于:2022-07-14 05:36

    ivermectin pour on for goats https://stromectoltrust.com/
    stromectol 3 mg tablet

  11. IziPharm发表于:2022-07-16 09:55

    amoxicillin without a doctor’s prescription https://pharmacyizi.com/

  12. IziPharm发表于:2022-07-16 11:15

    legal to buy prescription drugs from canada https://pharmacyizi.com/

  13. IziPharm发表于:2022-07-16 12:33

    fast ed meds online https://pharmacyizi.com/

  14. IziPharm发表于:2022-07-16 13:54

    sildenafil without a doctor’s prescription https://pharmacyizi.com/

  15. IziPharm发表于:2022-07-16 15:13

    carprofen without vet prescription https://pharmacyizi.com/

  16. IziPharm发表于:2022-07-16 16:33

    discount prescription drugs https://pharmacyizi.com/

  17. IziPharm发表于:2022-07-16 17:51

    buy prescription drugs online without https://pharmacyizi.com/

  18. IziPharm发表于:2022-07-16 19:14

    viagra without a doctor prescription walmart https://pharmacyizi.com/

  19. IziPharm发表于:2022-07-16 20:32

    ed meds online canada https://pharmacyizi.com/

  20. IziPharm发表于:2022-07-16 21:56

    buy anti biotics without prescription https://pharmacyizi.com/

  21. OnlinePha发表于:2022-07-22 08:26

    canadian pharmacy no rx needed https://onlinepharmacy.men/

  22. Pharmacy发表于:2022-07-22 12:45

    which online pharmacy is reliable recommended canadian pharmacies cheapest pharmacy

  23. Pharmacy发表于:2022-07-22 13:59

    no prescription required pharmacy online pharmacy fungal nail cheapest prescription pharmacy

  24. Pharmacy发表于:2022-07-22 15:11

    trusted online pharmacy mexican pharmacy canadian pharmacy 365

  25. Pharmacy发表于:2022-07-22 16:38

    canadian pharmacies online pharmacy website india onlinepharmaciescanada com

  26. Pharmacy发表于:2022-07-22 17:51

    web pharmacy online pharmacy pharmacy prices

  27. Pharmacy发表于:2022-07-22 19:29

    my canadian pharmacy reviews reliable canadian pharmacy generic viagra online canadian pharmacy

  28. Pharmacy发表于:2022-07-22 20:52

    tadalafil canadian pharmacy canada pharmacy not requiring prescription pharmacy online 365

  29. Pharmacy发表于:2022-07-22 22:15

    online pharmacy products reliable canadian online pharmacy best pharmacy

  30. Pharmacy发表于:2022-07-22 23:37

    canada pharmacy online real canadian pharmacy online pharmacy china

  31. Pharmacy发表于:2022-07-23 00:58

    medical mall pharmacy buy online pharmacy uk express scripts pharmacy

  32. CanadaDr发表于:2022-07-23 13:38

    online canadian drugstore https://canadiandrugs.best/

  33. Canadian发表于:2022-07-23 18:09

    prescription drugs without doctor approval prescription drugs canada buy online best canadian pharmacy online

  34. Canadian发表于:2022-07-23 19:33

    buy prescription drugs pain meds without written prescription best canadian pharmacy online

  35. Canadian发表于:2022-07-23 20:59

    comfortis without vet prescription amoxicillin without a doctor’s prescription mexican pharmacy without prescription

  36. Canadian发表于:2022-07-23 22:22

    prescription drugs without prior prescription prescription drugs online without mexican pharmacy without prescription

  37. Canadian发表于:2022-07-23 23:46

    online canadian drugstore cheap pet meds without vet prescription ed meds online without doctor prescription

  38. Canadian发表于:2022-07-24 01:10

    how to get prescription drugs without doctor carprofen without vet prescription anti fungal pills without prescription

  39. Canadian发表于:2022-07-24 02:34

    buy prescription drugs canadian online pharmacy cvs prescription prices without insurance

  40. Canadian发表于:2022-07-24 03:57

    canadian online drugs prescription drugs without prior prescription pet meds without vet prescription

  41. Canadian发表于:2022-07-24 05:21

    mexican pharmacy without prescription ed meds online without doctor prescription canadian drug pharmacy

  42. Canadian发表于:2022-07-24 06:44

    prescription meds without the prescriptions online canadian pharmacy buy prescription drugs without doctor

  43. Stromectol发表于:2022-07-29 13:54
  44. Stromectol发表于:2022-07-29 15:07
  45. Stromectol发表于:2022-07-29 16:20

    stromectol buy online uk: https://stromectolbestprice.com/

  46. Stromectol发表于:2022-07-29 17:52

    ivermectin for pregnant goats: https://stromectolbestprice.com/

  47. Stromectol发表于:2022-07-29 19:08

    ivermectin fungal infection: https://stromectolbestprice.com/

  48. Stromectol发表于:2022-07-29 20:29

    how much ivermectin paste to give a dog: https://stromectolbestprice.com/

  49. Stromectol发表于:2022-07-29 21:53

    ivermectin oral for dogs: https://stromectolbestprice.com/

  50. Stromectol发表于:2022-07-29 23:12

    ivermectin cream before and after: https://stromectolbestprice.com/

  51. Stromectol发表于:2022-07-30 00:37

    ivermectin brand names: https://stromectolbestprice.com/

  52. Stromectol发表于:2022-07-30 02:06

    ivermectin for foxes: https://stromectolbestprice.com/

  53. Stromectol发表于:2022-07-31 06:04

    ivermectin fda label https://stromectolbestprice.com/

  54. Stromectol发表于:2022-07-31 12:04

    ivermectin equine ivermectin pour on for ringworm in cattle ivermectin for cutaneous larva migrans

  55. Stromectol发表于:2022-07-31 13:30

    tractor supply ivermectin peak prosperity ivermectin ivermectin sheep drench tractor supply

  56. Stromectol发表于:2022-07-31 15:00

    how fast does ivermectin work ivermectin for cows ivermectin for sale for humans

  57. Stromectol发表于:2022-07-31 16:35

    ivermectin 12 mg price ivermectin side effects dogs ivermectin paste dosage for dogs

  58. Stromectol发表于:2022-07-31 18:15

    ivermectin injectable for dogs liquid ivermectin for cats ivermectin dose for scabies in humans

  59. Stromectol发表于:2022-07-31 19:55

    ivermectin pill stromectol buy ivermectin chicken wormer

  60. Stromectol发表于:2022-07-31 21:33

    how to dose ivermectin for humans ivermectin for demodex mites in dogs ivermectin for goats lice

  61. Stromectol发表于:2022-07-31 23:07

    ivermectin, metronidazole ivermectin tablets for humans ivermectin pour on tractor supply

  62. Stromectol发表于:2022-08-01 00:47

    cattle ivermectin for humans where to get ivermectin ivermectin dogs

  63. Drugsj发表于:2022-08-01 10:26

    viagra without doctor prescription – https://drugsbestprice.com/

  64. Drugsj发表于:2022-08-01 11:50

    amoxicillin without a doctor’s prescription – https://drugsbestprice.com/

  65. Drugsj发表于:2022-08-01 13:09

    comfortis for dogs without vet prescription – https://drugsbestprice.com/

  66. Drugsj发表于:2022-08-01 14:28

    buy anti biotics without prescription – https://drugsbestprice.com/

  67. Drugsj发表于:2022-08-01 15:47

    pain meds without written prescription – https://drugsbestprice.com/

  68. Drugsj发表于:2022-08-01 17:32

    prescription drugs without doctor approval – https://drugsbestprice.com/

  69. Drugsj发表于:2022-08-01 19:00

    ed meds online canada – https://drugsbestprice.com/

  70. Drugsj发表于:2022-08-01 20:32

    cheap pet meds without vet prescription – https://drugsbestprice.com/

  71. Drugsj发表于:2022-08-01 22:05

    viagra without a prescription – https://drugsbestprice.com/

  72. Drugsj发表于:2022-08-01 23:31

    prescription drugs online without doctor – https://drugsbestprice.com/

  73. DrugsOk发表于:2022-08-02 12:07

    viagra without a doctor prescription: discount prescription drugs cialis without a doctor’s prescription

  74. CanadaRx发表于:2022-08-03 18:22

    pet meds without vet prescription canada prescription drugs online without doctor – buy prescription drugs without doctor

  75. CanadaRx发表于:2022-08-03 19:52

    canada ed drugs tadalafil without a doctor’s prescription – buy cheap prescription drugs online

  76. CanadaRx发表于:2022-08-03 21:24

    non prescription ed drugs best canadian pharmacy online – mexican pharmacy without prescription

  77. CanadaRx发表于:2022-08-04 00:24

    cat antibiotics without pet prescription pain meds online without doctor prescription – online canadian drugstore

  78. CanadaRx发表于:2022-08-04 01:53

    dog antibiotics without vet prescription online canadian drugstore – canadian pharmacy online

  79. CanadaRx发表于:2022-08-04 03:24

    non prescription ed drugs pain meds without written prescription – discount prescription drugs

  80. CanadaRx发表于:2022-08-04 04:53

    prescription drugs online ed meds online without doctor prescription – canadian drug pharmacy

  81. CanadaRx发表于:2022-08-04 06:22

    best canadian pharmacy online prescription drugs – buy prescription drugs from india

  82. CanadaRx发表于:2022-08-04 07:51

    sildenafil without a doctor’s prescription prescription drugs online without doctor – comfortis without vet prescription