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