MySQL篇-数据校验规则总结

本文主要是介绍MySQL篇-数据校验规则总结，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1、MySQL库名、表名大小写验证规则设置

1.1 MySQL使用lower_case_table_names 该参数设置库名、表名大小写验证规则。lower_case_table_names 取值结果：1/0，配置文件中如果定义该参数值是1，则该参数值=1；如果定义该参数是除1以外的整数，则该参数值=0（非1即0）

现有1+N、生产环境的字符集是uft8，lower_case_table_names=1，不允许修改。若需要修改，必须得在新部署环境时单独修改。

（mysql5.7.27和mysql8.0.15 测试均符合上述内容）
    
    举例说明：

    ① lower_case_table_names = 0 库名、表名存储为给定的大小写，比较也是区分大小写的
        
        ###'数据库名、表名存储的时候是区分大小写的'
        mysql> create database Test2;
        Query OK, 1 row affected (0.00 sec)
        
        mysql> use Test2;
        Database changed
        mysql> create table Test(Id int(10) primary key,Name varchar(20));
        Query OK, 0 rows affected (0.06 sec)
        
        mysql> show create table Test\G
        *************************** 1. row ***************************
               Table: Test
        Create Table: CREATE TABLE `Test` (
          `Id` int(10) NOT NULL,
          `Name` varchar(20) DEFAULT NULL,
          PRIMARY KEY (`Id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        1 row in set (0.01 sec)
        mysql> show tables;
        +-----------------+
        | Tables_in_Test2 |
        +-----------------+
        | Test            |
        +-----------------+
        mysql> show databases;
        +------------------------+
        | Database               |
        +------------------------+
        | information_schema     |
        | Test2                  |
        | mysql                  |
        | #mysql50#pages-ibdata1 |
        | performance_schema     |
        | sys                    |
        +------------------------+
        6 rows in set (0.00 sec)
        
        ###'数据库名、表名比较的时候也是区分大小写的'
        
        mysql> use test2;
        ERROR 1049 (42000): Unknown database 'test2'
        mysql> use Test2;
        Database changed
        
        mysql> select * from Test;
        Empty set (0.00 sec)
        mysql> select * from test;
        ERROR 1146 (42S02): Table 'Test2.test' does not exist

    ② lower_case_table_names = 1 库名、表名在磁盘存储都是小写的，参数名和数据存放是区分大小写的，但是比较的时候不区分大小写;
    
        ###创建数据库Test5(大小写混合)
        mysql> create database Test5;
        Query OK, 1 row affected (0.01 sec)
        mysql> show databases;
        +------------------------+
        | Database               |
        +------------------------+
        | information_schema     |
        | mysql                  |
        | #mysql50#pages-ibdata1 |
        | performance_schema     |
        | sys                    |
        | test5                  |
        +------------------------+
        ###使用小写库名进入数据库
        mysql> use test5;
        Database changed
        ###创建表名（表名，字段名大小写混合）
        mysql> create table Test5(id int(10) primary key,Name varchar(20));
        Query OK, 0 rows affected (0.06 sec)
        ###查看库中的表，库名表名都是小写的
        mysql> show tables;
        +-----------------+
        | Tables_in_test5 |
        +-----------------+
        | test5           |
        +-----------------+
        ###插入数据使用的表名，字段都是小写的，数据是大小写混合的。
        mysql> insert into test5(id,name) values(1,'Aaa');
        ###查出来的结果，数据和字段显示都是大小写混合的
        Query OK, 1 row affected (0.03 sec)
        mysql> select * from test5 where name='aaa';
        +----+------+
        | id | Name |
        +----+------+
        |  1 | Aaa  |
        +----+------+


1.2 现有环境（1+N，生产）该参数默认配置都是 1。该参数不能在线修改，mysql8环境搭建成功后不可再次更改，mysql5.7可以重启数据库更改
    
        在线更改报错，mysql5.7可以重启生效：
        mysql> set global lower_case_table_names=0;
        ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable
        
        mysql8.0修改参数后重启失败，日志报错，与数据字典不兼容：
        Data Dictionary initialization failed.
        
        3）MySQL数据大小写验证规则
        不区分大小写：
        字符集：utf8     默认校验规则是：utf8_grneral_ci
        字符集：gbk      默认校验规则是：utf8_grneral_ci
        字符集：utf8mb4  默认校验规则是：utf8mb4_grneral_ci
        
        区分大小写：
        字符集：utf8     需设置collate : utf8_bin
        字符集：utf8mb4  需设置collate : utf8mb4_bin
        字符集：gbk      需设置collate : gbk_bin
        
        区分大小写的校验规则适用于 char，varchar，text字段

2、MySQL数据大小验证规则修改方法

现有1+N、生产环境的库名、表名、字段名、数据使用时默认不区分大小写。若要区分大小写，需要自定义校验规则，可参考下面的方法修改校验规则

2.1 数据库级别的区分大小写的规则创建与修改

    ① 创建 
        创建数据库时指定区分大小写的规则，然后新创建的表默认都是区分大小写的，但是历史表还是不区分大小写
        create database test5 character set utf8 collate=utf8_bin;
        
        mysql> create database test5 character set utf8 collate=utf8_bin;
        Query OK, 1 row affected (0.02 sec)
        
        mysql> use test5;
        Database changed
        mysql> create table test2(name varchar(20));
        Query OK, 0 rows affected (0.06 sec)
        
        mysql> show create table test2\G
        *************************** 1. row ***************************
               Table: test2
        Create Table: CREATE TABLE `test2` (
          `name` varchar(20) COLLATE utf8_bin DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
        1 row in set (0.00 sec)
    
    ② 修改
        修改数据库区分大小写规则：该库中历史表的校验规则不变，后续新建的表校验规则是 utf8_bin
        alter database test character set utf8 collate utf8_bin;
        
        修改数据库的校验规则
        mysql> alter database test character set utf8 collate utf8_bin;
        Query OK, 1 row affected (0.89 sec)
        
        历史表的校验规则：不区分大小写
        mysql> show create table test\G
        *************************** 1. row ***************************
               Table: test
        Create Table: CREATE TABLE `test` (
          `id` int(10) NOT NULL,
          `name` varchar(20) DEFAULT NULL,
          PRIMARY KEY (`id`),
          KEY `idx_name` (`name`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        1 row in set (0.00 sec)
        新建表，然后查看新表的校验规则：创建时未指定。但结果是区分大小写的
        mysql> create table test2(name varchar(20));
        Query OK, 0 rows affected (0.32 sec)
        
        mysql> show create table test2\G
        *************************** 1. row ***************************
               Table: test2
        Create Table: CREATE TABLE `test2` (
          `name` varchar(20) COLLATE utf8_bin DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin


2.2 表级别的区分大小写的规则创建与修改
    ① 创建
        mysql> create table test1(id int(10) primary key,name varchar(20)) character set utf8 collate utf8_bin;
        Query OK, 0 rows affected (0.08 sec)
        
        mysql> show create table test1\G
        *************************** 1. row ***************************
               Table: test1
        Create Table: CREATE TABLE `test1` (
          `id` int(10) NOT NULL,
          `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
        1 row in set (0.00 sec)
   
    ② 修改
        mysql> show create table test2\G
        *************************** 1. row ***************************
           Table: test2
        Create Table: CREATE TABLE `test2` (
          `id` int(10) NOT NULL,
          `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
          `gender` varchar(20) COLLATE utf8_bin DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
        1 row in set (0.00 sec)
        
        mysql> alter table test2 convert to character set utf8 collate utf8_general_ci;
        Query OK, 0 rows affected (0.12 sec)
        Records: 0  Duplicates: 0  Warnings: 0
        
        mysql> show create table test2\G
        *************************** 1. row ***************************
               Table: test2
        Create Table: CREATE TABLE `test2` (
          `id` int(10) NOT NULL,
          `name` varchar(20) DEFAULT NULL,
          `gender` varchar(20) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        1 row in set (0.00 sec)

        '指定区分大小写的修改表的方式根据字符集不同分为两种'：(utf8 = utf8mb3，utf8mb4是utf8mb3的升级版)
        alter table test3 convert to character set utf8mb4 collate utf8mb4_bin;
        alter table test3 convert to character set utf8 collate utf8_bin;


2.3 字段级别的区分大小写的规则创建与修改
    
    ① 创建
        mysql> create table test3(id int(10),name varchar(20) binary,gender varchar(20));
        Query OK, 0 rows affected (0.06 sec)
        
        mysql> show create table test3\G
        *************************** 1. row ***************************
               Table: test3
        Create Table: CREATE TABLE `test3` (
          `id` int(10) DEFAULT NULL,
          `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
          `gender` varchar(20) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        1 row in set (0.00 sec)
    ② 修改
        mysql> alter table test3 modify column name varchar(20) binary;
        Query OK, 0 rows affected (0.02 sec)
        Records: 0  Duplicates: 0  Warnings: 0
        
        mysql> show create table test3\G
        *************************** 1. row ***************************
               Table: test3
        Create Table: CREATE TABLE `test3` (
          `id` int(10) DEFAULT NULL,
          `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
          `gender` varchar(20) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8
        1 row in set (0.01 sec)
        
        修改字段方式有两种，指定区分大小写：直接指定是binary或者指定collate
        alter table test3 modify column name varchar(20) character set utf8 collate utf8_bin;
        alter table test3 modify column name varchar(20) binary;

2.4 历史数据表，不区分大小写，但是还需要实现大小写，可以使用binary 参数实现
    mysql> select * from test where name='caps';
    +----+------+
    | id | name |
    +----+------+
    |  5 | Caps |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql> select * from test where binary name='caps';
    Empty set (0.00 sec)
    
    此种场景下，使用binary，无法使用索引
    
2.5 校验规则继承顺序

    mysql允许在四个级别指定字符集和校对规则：mysql实例，数据库，表和列。

    字段 > 表级别 > 数据库级别 > mysql实例
    
    创建的表的字段未指定则默认继承表的校验规则；
    创建的表未指定则默认继承数据库的校验规则；
    创建的数据库未指定时则默认继承mysql实例的校验规则。


已创建的表直接修改：alter table test convert to character set utf8mb4 collate utf8mb4_bin;
新建表时设置校验规则：create table test(id int(10) primary key,name varchar(20)) character set utf8 collate utf8_bin;

库级别的验证规则可以改，修改之后，新建的表不设置character时，自动区分大小写的。
库级别的修改，如果需要改，我们改一下，alter database test character set utf8 collate utf8_bin;

这篇关于MySQL篇-数据校验规则总结的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！