【MySQL】Linux中MySQL的使用及配置

一、字符集修改

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: 'xE5x8Dx81xE5xB9xB4' 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

如有错误，欢迎指正！！！