一、字符集修改
1、MySQL8.0之前,字符集都默认是latin1,从MySQL8.0开始,字符集utf8mb4,这意味着,在默认情况下,MySQL8.0之前都是不支持中文的
# 查看字符集信息,主要是character_set_server和character_set_database
show variables like '%character%';
# 创建一个database,进行插入中文测试
create database dbtest1;
create table test1(
id INT,
name VARCHAR(10)
);
insert into test1(id,name)VALUES(1,'decade');
insert into test1(id,name)VALUES(2,'十年');
我们发现,在MySQL5.7中,如果不修改字符集,插入中文时会报错
insert into test1(id,name)VALUES(2,'十年')
1366 - Incorrect string value: '\xE5\x8D\x81\xE5\xB9\xB4' for column 'name' at row 1
2、修改字符集
Windows系统中,我们要修改MySQL的字符集,需要修改my.ini文件
而在Linux系统中,MySQL的配置文件为/etc/my.cnf
vim /etc/my.cnf
# 输入i,进入编辑模式,在mysqld标签下加入如下配置,设置服务器级别的字符集
character_set_server=utf8
# 输入esc退出编辑模式,:wq!,保存文件的变更
# 保存后,重启MySQL服务
systemctl restart mysqld
注意:上述指令不会
对已经创建
的database的字符集产生影响
在建表时,如果没有显式指定
字符集,那么新建表的字符集会和当前database
的字符集保持一致
3、修改已创建的database或者表的字符集
# 修改database的字符集
ALTER DATABASE dbtest1 CHARACTER set 'utf8';
# 查看数据库database的字符集
show create DATABASE dbtest1;
# 修改已创建表的字符集
ALTER TABLE test1 CONVERT to CHARACTER set 'utf8';
# 修改已创建表的字符集和比较规则
ALTER TABLE test1 CHARACTER set 'utf8' COLLATE 'utf8_general_ci';
# 查看已创建表的字符集
show create table test1;
# 修改字段的字符集
alter table test1 modify name varchar(10) character set utf8;
4、MySQL存在四个级别的字符集
- 服务器级别
- 数据库级别
- 表级别
- 列级别
优先级,列>表>数据库>服务器,如果优先级高的没有设置字符集,那么默认使用它上一级的字符集
二、SQL大小写规范
1、在SQL中,关键字(如SELECT、WHERE、ORDER、GROUP BY等)以及函数名(如ABS、ROUND、MAX)是不区分大小写的
2、Windows默认对大小写不敏感,Linux对大小写是敏感的
分别在Windows和Linux中执行SHOW VARIABLES LIKE '%lower_case_table_names%';
,结果如下
- value默认为0,表示大小写敏感
- value设置为1表示不敏感,创建的数据库和表都是以小写的形式放在磁盘中,sql语句都是先转换成小写再去执行
- value设置为2,数据库和表按照创建时的大小写进行存放,sql的执行也会先转换成小写
3、Linux中MySQL的大小写规则
- 数据库名、表名、表的别名、变量名严格区分大小写
- 关键字、函数名在SQL中不区分大小写
- 列名、列的别名忽略大小写
4、SQL编写规范
- 关键字、函数名全部大写
- 数据库名、表名、表的别名、字段名、字段别名等全部小写,便于和关键字、函数名进行区分
- SQL语句以分号结尾
三、sql_mode
1、sql_mode会影响MySQL支持的语法以及数据校验的严格性
2、宽松模式和严格模式
- 如果sql_mode是宽松模式,当插入的数据不满足要求时,也可能会被接受,而且不会报错
- 如果是严格模式,当试图插入错误数据时,数据库不会接受,会报error错误,并且事务回滚
3、sql_mode的查看与修改
- 查看
mysql> select @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
- 临时修改:修改session会话或者全局global变量
# 只在当前会话中有效
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
# 只在当前服务中有效,MySQL服务重启就会失效
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';
- 永久修改:修改配置文件my.cnf并重启MySQL服务
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
如有错误,欢迎指正!!!