第01章 Linux下MySQL的安装与使用【1.MySQL架构篇】【MySQL高级】4

简介: 第01章 Linux下MySQL的安装与使用【1.MySQL架构篇】【MySQL高级】4

常用操作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 段落

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
12天前
|
存储 SQL 关系型数据库
Mysql高可用架构方案
本文阐述了Mysql高可用架构方案,介绍了 主从模式,MHA模式,MMM模式,MGR模式 方案的实现方式,没有哪个方案是完美的,开发人员在选择何种方案应用到项目中也没有标准答案,合适的才是最好的。
69 3
Mysql高可用架构方案
|
1月前
|
监控 关系型数据库 MySQL
深入了解MySQL主从复制:构建高效稳定的数据同步架构
深入了解MySQL主从复制:构建高效稳定的数据同步架构
120 1
|
2月前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
3月前
|
Ubuntu Linux
查看Linux系统架构的命令,查看linux系统是哪种架构:AMD、ARM、x86、x86_64、pcc 或 查看Ubuntu的版本号
查看Linux系统架构的命令,查看linux系统是哪种架构:AMD、ARM、x86、x86_64、pcc 或 查看Ubuntu的版本号
839 3
|
7天前
|
SQL 存储 缓存
【赵渝强老师】MySQL的体系架构
本文介绍了MySQL的体系架构,包括Server层的7个主要组件(Connectors、Connection Pool、Management Service & Utilities、SQL Interface、Parser、Optimizer、Query Caches & Buffers)及其作用,以及存储引擎层的支持情况,重点介绍了InnoDB存储引擎。文中还提供了相关图片和视频讲解。
【赵渝强老师】MySQL的体系架构
|
5天前
|
缓存 运维 网络协议
深入Linux内核架构:操作系统的核心奥秘
深入Linux内核架构:操作系统的核心奥秘
22 2
|
1月前
|
SQL 分布式计算 Hadoop
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
Hadoop-12-Hive 基本介绍 下载安装配置 MariaDB安装 3台云服务Hadoop集群 架构图 对比SQL HQL
60 3
|
2月前
|
编解码 Linux 开发工具
Linux平台x86_64|aarch64架构RTMP推送|轻量级RTSP服务模块集成说明
支持x64_64架构、aarch64架构(需要glibc-2.21及以上版本的Linux系统, 需要libX11.so.6, 需要GLib–2.0, 需安装 libstdc++.so.6.0.21、GLIBCXX_3.4.21、 CXXABI_1.3.9)。
|
3月前
|
负载均衡 网络协议 Linux
在Linux中,常用WEB服务器负载架构有哪些?
在Linux中,常用WEB服务器负载架构有哪些?
|
3月前
|
存储 前端开发 关系型数据库
Linux 技术架构:前端、后端与数据库的完美融合
【8月更文挑战第25天】本文深入剖析了Linux操作系统的技术架构,重点介绍了前端、后端及数据库三大核心组成部分。Linux前端技术不仅涵盖了图形用户界面(GUI),包括GNOME、KDE等桌面环境,还涉及HTML、CSS、JavaScript等Web前端技术及其相关框架。后端技术则聚焦于Python、Java等多种编程语言、Apache和Nginx等Web服务器以及MySQL、PostgreSQL等数据库管理系统。Linux数据库技术覆盖了关系型和非关系型数据库,如MySQL、MongoDB等,并提供了多种数据库管理工具。
93 0