Linux下Mysql的介绍、安装、配置及使用

书籍推荐

参考链接：#MySQL数据库（mysql安装/基础/高级/优化）

前言、MySQL简介

1、什么是Mysql

MySQL 是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于Oracle 公司。
Mysql 是开源的，可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。
MySQL 使用标准的SQL数据语言形式。
Mysql 可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和Tcl 等。
MySQL 支持大型数据库，支持5000 万条记录的数据仓库，32 位系统表文件最大可支持4GB，64 位系统支持最大的表文件为8TB。

2、Mysql内功修炼

完整的MySQL优化需要很深的功底，---DBA【数据库系统管理】

数据库内部结构和原理
数据库建模优化
数据库索引建立
SQL语句优化
SQL编程(自定义函数、存储过程、触发器、定时任务)
mysql服务器的安装配置
数据库的性能监控分析与系统优化
各种参数常量设定
主从复制
分布式架构搭建、垂直切割和水平切割
数据迁移
容灾备份和恢复
shell 或python 等脚本语言开发
对开源数据库进行二次开发

1、环境准备

1.1、Linux系统及工具的准备

采用Centos7.6(建议买一台云服务器)
安装Xshell 和 Xftp 等远程访问和文件上传的工具
本次实验采用RPM方式安装mysql8.0和mysql5.7

1.2、检查当前系统是否安装过Mysql

如果你是用rpm安装，检查一下RPM PACKAGE：Linux下查看安装过的所有MySQL版本及rpm里有没有MySQL

rpm -qa | grep -i mysql # -i 忽略大小写

检查mysql service：(启动mysql服务的命令：6是service，7是systemctl )

systemctl status mysqld.service

如果存在mysql-libs的旧版本包，显示如下：

[root@HDU001 ~]# rpm -qa | grep -i mysql
mysql-community-client-plugins-8.0.26-1.el7.x86_64
mysql-community-client-8.0.26-1.el7.x86_64
mysql-community-server-8.0.26-1.el7.x86_64
mysql-community-common-8.0.26-1.el7.x86_64
mysql-community-libs-8.0.26-1.el7.x86_64
[root@HDU001 ~]#

2、在Linux上安装Mysql-8.0

1、打开官网地址：downloads.mysql.com/archives/co… 选择8.0版本，并下载RPM Bundle到本地即可，如下所示：

2、将该RPM安装包下载到本地以后，直接解压，并通过XFTP工具将解压后里面如下几个RPM压缩包(包含客户端和服务端以及一些相关依赖)直接上传到Centos的opt目录下

上传完毕后，可以通过命令进入opt目录下查看(这里采用XShell工具进行远程登录)

[root@HDU001 opt]# cd /opt
[root@HDU001 opt]# ll
总用量 500116
-rw-r--r--. 1 root root  47836256 1月  13 16:03 mysql-community-client-8.0.26-1.el7.x86_64.rpm
-rw-r--r--. 1 root root   4694644 1月  13 16:03 mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
-rw-r--r--. 1 root root    634632 1月  13 16:03 mysql-community-common-8.0.26-1.el7.x86_64.rpm
-rw-r--r--. 1 root root   4243808 1月  13 16:03 mysql-community-libs-8.0.26-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 454689776 1月  13 16:03 mysql-community-server-8.0.26-1.el7.x86_64.rpm
drwxr-xr-x. 2 root root      4096 10月 31 2018 rh

3、在CentOS7下检查MySQL依赖

检查/tmp临时目录权限（必不可少）：由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行：
```
[root@HDU001 opt]# chmod -R 777 /tmp
```

安装前，检查依赖，如果存在libaio包，如下：

[root@HDU001 opt]# rpm -qa|grep libaio
libaio-0.3.109-13.el7.x86_64

如果存在net-tools包，如下：

[root@HDU001 opt]# rpm -qa|grep net-tools
net-tools-2.0-0.24.20131004git.el7.x86_64

如果不存在则需要到centos安装盘里进行rpm安装。安装linux如果带图形化界面，这些都是安装好的。

4、将相应安装程序拷贝到/opt目录以后，在mysql的安装文件目录(这里mysql安装文件的目录就是/opt)下执行rpm指令进行安装：（必须按照顺序执行）

[root@HDU001 opt]# rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm
[root@HDU001 opt]# rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
[root@HDU001 opt]# rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
[root@HDU001 opt]# rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
[root@HDU001 opt]# rpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm

注意: 如在检查工作时，没有检查mysql依赖环境在安装 mysql-community-server会报错

rpm 是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。
-i , --install 安装软件包
-v , --verbose 提供更多的详细信息输出
-h , --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

安装过程中可能的报错信息：

[root@HDU001 opt]# rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
警告：mysql-community-libs-8.0.26-1.el7.x86_64.rpm: 头V3 DSA/SHA256 Signature, 密钥 ID 5072e1f5: NOKEY
错误：依赖检测失败：
	mariadb-libs 被 mysql-community-libs-8.0.26-1.el7.x86_64 取代

运行下面命令即可解决，即清除之前安装过的依赖即可

[root@HDU001 opt]# yum remove mysql-libs

5、查看MySQL版本，执行如下命令，如果成功表示安装mysql成功。类似java -version如果打出版本等信息

mysql --version
或
mysqladmin --version

[root@HDU001 opt]# mysql --version
mysql  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

或者也可以通过rpm 命令来查看是否安装成功。需要增加 -i 不用去区分大小写，否则搜索不到。

[root@HDU001 opt]# rpm -qa|grep -i mysql
mysql-community-client-plugins-8.0.26-1.el7.x86_64
mysql-community-client-8.0.26-1.el7.x86_64
mysql-community-server-8.0.26-1.el7.x86_64
mysql-community-common-8.0.26-1.el7.x86_64
mysql-community-libs-8.0.26-1.el7.x86_64

6、Mysql服务的初始化

为了保证数据库目录与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化：

[root@HDU001 opt]# mysqld --initialize --user=mysql

说明： --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登录后你需要设置一个新的密码。生成的临时密码会往日志中记录一份。

查看密码：

[root@HDU001 opt]# cat /var/log/mysqld.log
2022-01-13T08:19:35.069398Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.26) initializing of server in progress as process 66400
2022-01-13T08:19:35.086319Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-01-13T08:19:35.498289Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-01-13T08:19:36.659628Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-01-13T08:19:36.660307Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-01-13T08:19:36.773088Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Qd;&LR9w0Qpu
2022-01-13T08:20:14.755972Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.26) initializing of server in progress as process 66461
..........

root@localhost: 后面就是初始化的密码

7、启动MySQL服务，查看状态

#加不加.service后缀都可以
启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：systemctl restart mysqld.service
查看状态：systemctl status mysqld.service

mysqld 这个可执行文件就代表着 MySQL 服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。

8、查看进程：可以发现Mysql已经启动了

ps -ef | grep -i mysql

9、查看MySQL服务是否开机自启动

默认是enabled，设置成了自启动，则我们下次使用命令行登录Mysql的时候就直接输入密码账户即可登录，不需要再次启动Mysql服务了。

[root@HDU001 opt]# systemctl list-unit-files|grep mysqld.service
mysqld.service                                enabled

如不是enabled可以运行如下命令设置自启动

systemctl enable mysqld.service

如果希望不进行自启动，运行如下命令设置

systemctl disable mysqld.service

10、MySQL登录

首次登录，通过 mysql -hlocalhost -P3306 -uroot -p 进行登录，在Enter password：录入初始化密码即可

[root@HDU001 opt]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看数据库

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

11、修改root用户密码

因为初始化密码默认是过期的，所以查看数据库会报错

修改密码：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456789';
Query OK, 0 rows affected (0.00 sec)

5.7版本之后（不含5.7），mysql加入了全新的密码安全机制，设置新密码太简单会报错。改为更复杂的密码规则之后，设置成功，可以正常使用Mysql数据库了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

12、重新启动Centos，重新远程登录，输入设置的新密码后就可以直接进行Mysql的登录(设置了自启动)

[root@HDU001 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)

mysql>quit;
bye

3、在Linux上安装Mysql-5.7

建议装在另外一台虚拟机上，安装步骤一样：差别如下：

1、选择的是5.7.28版本

2、下载该RPM包解压后要上传的文件为：

补充：centos7系统，是systemctl来管理服务。可以通过systemctl enable mysql来设置 mysql开机启动自动，也可以通过systemctl list-unit-files |grep mysql查看 centos7的开机启动项目

4、SQLyog/Navicat实现MySQL8.0和5.7的远程连接

4.1、当前问题

在用SQLyog或Navicat中配置远程连接Mysql数据库时遇到如下报错信息，这是由于Mysql配置了不支持远程连接引起的。

4.2、问题排查-确认网络

在远程机器上使用ping ip地址保证网络畅通

C:\Users\lemon>ping 192.168.62.128

正在 Ping 192.168.62.128 具有 32 字节的数据:
来自 192.168.62.128 的回复: 字节=32 时间=1ms TTL=64
来自 192.168.62.128 的回复: 字节=32 时间=1ms TTL=64
来自 192.168.62.128 的回复: 字节=32 时间=1ms TTL=64
来自 192.168.62.128 的回复: 字节=32 时间=1ms TTL=64

192.168.62.128 的 Ping 统计信息:
    数据包: 已发送 = 4，已接收 = 4，丢失 = 0 (0% 丢失)，
往返行程的估计时间(以毫秒为单位):
    最短 = 1ms，最长 = 1ms，平均 = 1ms

在远程机器上使用telnet命令：telnet ip地址端口号，保证端口号3306开放访问

C:\Users\lemon>telnet 192.168.62.128 3306
正在连接192.168.62.128...无法打开到主机的连接。 在端口 3306: 连接失败

没错，造成这种原因的就是我们熟悉的防火墙打开或端口号未开放造成的问题

4.3、关闭防火墙

CentOS6 ：

service iptables stop

CentOS7：

systemctl start firewalld.service 
systemctl status firewalld.service 
systemctl stop firewalld.service 
#设置开机启用防火墙 
systemctl enable firewalld.service 
#设置开机禁用防火墙 
systemctl disable firewalld.service

但是问题还是没有解决

4.4、开放端口

当然我们也可以不用关闭防火墙，直接开放3306端口号即可，但是问题也是一样

查看开放的端口号

firewall-cmd --list-all

设置开放的端口号

firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent

重启防火墙

firewall-cmd --reload

4.5、问题解决--Linux下修改配置

默认情况下，mysql是不允许root用户进行远程连接的，如下所示，我们查看Mysql里面的默认数据库mysql，里面有一张user表，该表里面就保存着允许登录的所有用户信息(基础总结的时候提到过)

[root@HDU001 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.06 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.01 sec)

mysql>

可以看到root用户的当前主机配置信息为localhost，因此意味着root用户必须在mysql所在的机器即本机上才能登录，所以不允许远程机器连接

解决：修改Host为通配符%

Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。 user=root Host=localhost表示只能通过本机客户端去访问。而 % 是个 通配符 ，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果 Host=% ，表示所有IP都有连接权限。

注意：在生产环境下不能为了省事将host设置为%，这样做会存在安全问题，具体的设置可以根据生产环境的IP进行设置。

update user set host = '%' where user ='root';

Host设置了“%”后便可以允许root用户进行远程访问。

Host修改完成后记得执行flush privileges使配置立即生效： use mysql; select Host,User from user;

flush privileges;

测试

如果是 MySQL5.7 版本，接下来就可以使用SQLyog或者Navicat成功连接至MySQL了。
如果是 MySQL8 版本，连接时还会出现如下问题：

配置新连接报错：错误号码 2058，分析是 mysql 密码加密方法变了。

解决方法：Linux下 mysql -u root -p 登录你的 mysql 数据库，然后执行这条SQL：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

然后在重新配置SQLyog的连接，则可连接成功了，OK。

5、字符集的相关操作

5.1 修改MySQL5.7字符集

1、修改步骤

在MySQL 8.0版本之前，默认字符集为latin1 ，utf8字符集指向的是utf8mb3 。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会在插入中文的时候出现乱码的问题。从MySQL8.0开始，数据库的默认编码将改为utf8mb4，从而避免上述乱码的问题。

操作1：查看默认使用的字符集

show variables like 'character%';
# 或者
show variables like '%char%';

MySQL8.0中执行：

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.06 sec)

MySQL5.7中执行：

从上面来看，MySQL 5.7 默认的客户端和服务器都用了latin1 ，不支持中文，保存中文会报错。MySQL5.7截图如下：

因为默认情况下，创建表使用的是latin1 。如下：

操作2：在配置文件my.cnf中修改字符集

vim /etc/my.cnf

在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：这是一种永久修改的方法，即服务器重启以后不会失效，但是建议重新复制一份配置文件再进行配置使用

character_set_server=utf8

操作3：重新启动MySQL服务

systemctl restart mysqld

重启数据库，再次查看：

# 1、进入到mysql数据库并查看字符集
# show variables like 'character%';
# show variables like '%char%';

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

注意： 安装MySQL完毕之后，第一件事就是修改字符集编码。 已经创建的数据库、表的设定不会发生变化，配置文件的参数修改只对新建的数据库有效！参考下面

2、已有库&表字符集的变更

MySQL5.7版本中，以前创建的库，创建的表字符集还是latin1。

修改已创建数据库的字符集

alter database dbtest1 character set 'utf8';

修改已创建数据表的字符集

alter table t_emp convert to character set 'utf8';

修改已经乱码数据

无论是修改mysql 配置文件或是修改已创建的库、表字符集，都无法改变已经变成乱码的数据。只能删除数据重新插入或更新数据或重新建表才可以完全解决

即原有的数据如果是用非'utf8'编码的话，数据本身编码不会发生改变。已有数据需要导出或删除，然后重新插入。

常用命令总结：

SQL 语句	描述	备注
show databases	列出所有数据库
create database 库名	创建一个数据库
create database 库名 character set utf8	创建数据库，顺便执行字符集为utf-8
show create database 库名	查看数据库的字符集
show variables like ‘%char%’	查询所有跟字符集相关的信息
set [字符集属性]=utf8	设置相应的属性为utf8	只是临时修改，当前有效。服务重启后，失效。
alter database 库名 character set 'utf8'	修改数据库的字符集
alter table 表名 convert to character set'utf8'	修改表的字符集

5.2 各级别的字符集

MySQL有4个级别的字符集和比较规则，分别是：

服务器级别
数据库级别
表级别
列级别

执行如下SQL语句：

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.06 sec)

character_set_server：服务器级别的字符集
character_set_database：当前数据库的字符集
character_set_client：服务器解码请求时使用的字符集
character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results：服务器向客户端返回数据时使用的字符集

1、服务器级别

character_set_server ：服务器级别的字符集。我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET 语句修改这两个变量的值。比如我们可以在配置文件中这样写：

[server]
character_set_server=gbk # 默认字符集
collation_server=gbk_chinese_ci #对应的默认的比较规则

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

2、数据库级别

character_set_database ：当前数据库的字符集。我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];
ALTER DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

3、表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)
	[[DEFAULT] CHARACTER SET 字符集名称]
	[COLLATE 比较规则名称]]
ALTER TABLE 表名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[COLLATE 比较规则名称]

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

4、列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(
	列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
	其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

提示

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

小结

我们介绍的这4个级别字符集和比较规则的联系如下：

如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表t中插入一条记录：

mysql> INSERT INTO t(col) VALUES('我们');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t;
+--------+
| s |
+--------+
| 我们 |
+--------+
1 row in set (0.00 sec)

首先列col 使用的字符集是gbk ，一个字符'我' 在gbk 中的编码为0xCED2 ，占用两个字节，两个字符的实际数据就占用4个字节。如果把该列的字符集修改为utf8 的话，这两个字符就实际占用6个字节

6、SQL大小写规范

6.1 Windows和Linux平台区别

在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关键字，以及 ABS、MOD、ROUND、MAX 等函数名。不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大小写问题。windows系统默认大小写不敏感，但是linux系统是大小写敏感的。通过如下命令查看：

SHOW VARIABLES LIKE '%lower_case_table_names%'

Windows系统下：

mysql> SHOW VARIABLES LIKE '%lower_case_table_names%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set, 1 warning (0.21 sec)

Linux系统下：

mysql> SHOW VARIABLES LIKE '%lower_case_table_names%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.02 sec)

lower_case_table_names参数值的设置：

默认为0，大小写敏感。
设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找。
设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

数据库名、表名、表的别名、变量名是严格区分大小写的；
关键字、函数名称在 SQL 中不区分大小写；
列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；
MySQL在Windows的环境下全部不区分大小写

6.2 Linux下大小写规则设置(了解)

当想设置为大小写不敏感时，要在 my.cnf 这个配置文件 [mysqld] 中加入lower_case_table_names=1 ，然后重启服务器。

但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。
此参数适用于MySQL5.7。在MySQL 8下禁止在重新启动 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 语句必须以分号结尾。

数据库名、表名和字段名在 Linux MySQL 环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

7、sql_mode的合理设置

7.1、介绍

sql_mode是个很容易被忽视的变量，5.6默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式。所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

sql model 常用来解决下面几类问题：

通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。
通过设置sql model 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql 进行较大的修改。
在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中。

注意： MySQL5.6和MySQL5.7默认的sql_mode模式参数是不一样的

5.6的mode是NO_ENGINE_SUBSTITUTION，其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。
5.7的mode是STRICT_TRANS_TABLES，也就是严格模式。用于进行数据的严格校验，错误数据不能插入，报error，并且事务回滚

7.2、宽松模式 vs 严格模式

宽松模式：

如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。

举例：我在创建一个表时，该表中有一个字段为name，给name设置的字段类型时char(10)，如果我在插入数据的时候，其中name这个字段对应的有一条数据的长度超过了10 ，例如'1234567890abc'，超过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为了'1234567890'，而'abc'就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没有报错，并且mysql自行处理并接受了，这就是宽松模式的效果。

应用场景：通过设置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、模式查看和设置

查看当前的sql_mode

select @@session.sql_mode
select @@global.sql_mode
#或者
show variables like 'sql_mode';

默认情况下就是严格模式

模式设置

临时设置方式：设置当前窗口中设置sql_mode

SET GLOBAL sql_mode = 'modes...'; #全局  
SET SESSION sql_mode = 'modes...'; #当前会话  //默认就是session

举例

#改为严格模式。此方法只在当前会话中生效，关闭当前会话就不生效了。
set SESSION sql_mode='STRICT_TRANS_TABLES'; 
#改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。
set GLOBAL sql_mode='STRICT_TRANS_TABLES';

永久设置方式：在/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_ENGINE_SUBSTITUTION

然后重启MySQL 。

当然生产环境上是禁止重启MySQL服务的，所以采用临时设置方式 + 永久设置方式来解决线上的问题，那么即便是有一天真的重启了MySQL服务，也会永久生效了。

7.4、sql_mode 的影响案例：group by 查询语法错误

CREATE TABLE mytbl2 (id INT,NAME VARCHAR(200),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);

查询每个 dept 中年龄最大的人：

SELECT NAME,dept,MAX(age) FROM mytbl2

正确写法

SELECT id,name,ab.dept,ab.maxage FROM mytbl2 m INNER JOIN(SELECT dept,MAX(age)maxage FROM mytbl2
GROUP BY dept)ab ON ab.dept=m.dept AND m.age=ab.max

7.5、sql_mode常用值

值	说明
ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO	该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了
STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
NO_ZERO_IN_DATE	设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
ERROR_FOR_DIVISION_BY_ZERO	在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER	禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT	将"\|\|"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES	启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

高级篇(day01)-Linux下Mysql的介绍、安装、配置及使用