常用操作3
#查看表的字符集 SHOW CREATE TABLE emp1;
#查看表的比较规则 SHOW TABLE STATUS FROM dbtest1 LIKE 'emp1';
#修改表的字符集和比较规则 ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
5.4 请求到响应过程中字符集的变化
略
同一编码解码的字符集需一样
经验
都设置成utf8
SET NAMES utf8
6.SQL大小写规范
6.1 Windows和Linux平台区别
在SQL中，关键字和函数名是不区分大小写的。
大小写规范：
Windows系统默认大小写不敏感
Linux系统默认大小写敏感
通过命令查看：
SHOW VARIABLES LIKE '%lower_case_table_name%';
- Windows 系统下
Linux 系统下：
lower_case_table_names参数值的设置：
默认为0，大小写敏感。
设置1， 大小写不敏感。创建的表，数据库都是以小写的形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找。
设置2，创建的表和数据库依据语句上的格式存放，凡是查找都是转换为小写进行的。
两个平台上SQL大小写的区别具体来说:
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的:
1、数据库名、表名、表的别名、变量名是严格区分大小写的;
2、关键字、函数名称在SQL中不区分大小写;
3、列名(或字段名)与列的别名(或字段别名)在所有的情况下均是忽略大小写的;
MySQL在Windows的环境下全部不区分大小写
6.2Linux下大小写规则设置
当想设置为大小写不敏感时，要在my.cnf这个配置文件[mysqld]中加入lower_case_table_names=1，然后重启服务器。
但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。
此参数适用于MySQL5.7。在MySQL8下禁止在重新启动MySQL服务时将lower_case_table_names设置成不同于初始化MySQL服务时设置的lower_case_table_names值。如果非要将MySQL8设置为大小写不敏感，具体步骤为:
1、停止MySQL服务 2、删除数据目录，即删除/var/lib/mysql目录 3、在MySQL配置文件(/etc/my.cnf)中添加lower_case_table_names=1 4、启动MySQL服务
注意：在进行数据库参数的设置之前，需要掌握这个参数带来的影响，切不可盲目设置
6.3 SQL编写建议
如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议:
关键字和函数名称全部大写;
数据库名、表名、表别名、字段名、字段别名等全部小写;
SQL语句必须以分号结尾。
数据库名、表名和字段名在LinuxMySQL环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。
虽然关键字和函数名称在SQL中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。
7. sql_mode的合理设置
7.1 介绍
sql_mode会影响MySQL支持的SQL语法以及它执行的数据验证检查。通过设置sql_mode,可以完成不同严格程度的数据校验，有效地保障数据准确性。
MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体取决于sql_mode系统变量的值。
MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的:
5.6的mode默认值为空(即:NO_ENGINE_SUBSTITUTION)，其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。
5.7的mode是STRICTITRANS_TABLES，也就是严格模式。用于进行数据的严格校验，错误数据不能插入，报 error(错误)，并且事务回滚。
7.2宽松模式vs严格模式
宽松模式:
如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。
举例:我在创建一个表时，该表中有一个字段为name，给name设置的字段类型时char(10)，如果我在插入数 据的时候，其中name这个字段对应的有一条数据的长度超过了10，例如’1234567890abc，超过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为了’1234567890，而’abc’就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没有报错，并且mysqI自行处理并接受了，这就 是宽松模式的效果。
应用场景:通过设置sql mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql进行较大的修改。
严格模式:
出现上面宽松模式的错误，应该报错才对，所以MySQL5.7版本就将sql_mode默认值改为了严格模式。所以在生产等环境中，我们必须采用的是严格模式，进而开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可 以发现问题。并且我们即便是用的MySQL5.6，也应该自行将其改为严格模式。
开发经验:MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发中，我们应该在自己开发的项目程序级别将这些校验给做了，虽然写项目的时候麻烦了一些步骤，但是这样做之后，我们在进 行数据库迁移或者在项目的迁移时，就会方便很多。
改为严格模式后可能会存在的问题:
若设置模式中包含了NO_ZERO_DATE，那么MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警 告。例如，表中含字段TIMESTAMP列(如果未声明为NULL或显示DEFAULT子句)将自动分配DEFAULT 0000-00-00 00:00:00’(零时间戳)，这显然是不满足sql_mode中的NO_ZERO_DATE而报错。
7.3 宽松模式再举例
7.4 模式查看和设置
- 查看当前的sql-mode
select @@session.sql_mode ; select @@global.sql_mode ; #或者 show variable like 'sql_mode';
- 临时设置方式：设置当前窗口中设置sql_mode
select @@global.sql_mode ; #全局 # 重启mysql服务就不生效了 select @@session.sql_mode ; #当前会话 # 关闭当前会话就不生效了
- 永久设置方式：在/etc/my.cnf中配置sql_mode
在my.cnf文件（Windows系统是my.ini文件），新增：
[mysqld] 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'
然后重启mysql
当然生产环境上是禁止重启mysql服务的，所以采用临时设置方式+永久设置方式来解决线上的问题，那么即便有一天真的重启了mysql服务，也会永久生效了。
- 测试数据：
CREATE TABLE mytbl2(id INT,NAME VARCHAR(16),age INT,dept INT); INSERT INTO mytbl2 VALUES(1, 'zhang3',33,101); INSERT INTO mytbl2 VALUES(2,'li4',34,101); INSERT INTO mytbl2 VALUES(3,'wang5',34,102); INSERT INTO mytbl2 VALUES(4,'zhao6',34,102); INSERT INTO mytbl2 VALUES(5,'tian7',36,102); #查询每个部门年龄最大的人 SELECT NAME, dept,MAX(age) FROM mytbl2 GROUP BY dept;
报错：
# 设置sql_mode SET SESSION sql_mode='';
再查询，结果不一定对
INSERT INTO mytbl2 (id,NAME,age) VALUES(6,'Tom','aaa'); SELECT * FROM mytbl2;
没有报错
7.5 sql_mode的常用值
略
最后
p97~p103
2022/7/28 12:27
Markdown 18369 字数 871 行数
HTML 11368 字数 423 段落
