程序员scholar
  • MySQL
  • MySQL高级
scholar
2023-08-29
目录

第01章_Linux下安装MySQL

友情提示

转载须知: 以下所有文章整理于B站宋红康老师的《MySQL数据库入门到大牛》。MySQL (opens new window)

# 1. 安装前说明

# 1.1 查看是否安装过MySQL

  • 如果你是用rpm安装, 检查一下RPM PACKAGE：
rpm -qa | grep -i mysql # -i 忽略大小写
1
  • 检查mysql service：
systemctl status mysqld.service
1

# 1.2 MySQL的卸载

1. 关闭 mysql 服务

systemctl stop mysqld.service
1

2. 查看当前 mysql 安装状况

rpm -qa | grep -i mysql
# 或
yum list installed | grep mysql
1
2
3

3. 卸载上述命令查询出的已安装程序

yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx
1

务必卸载干净，反复执行rpm -qa | grep -i mysql确认是否有卸载残留

4. 删除 mysql 相关文件

  • 查找相关文件
find / -name mysql
1
  • 删除上述命令查找出的相关文件
rm -rf xxx
1

5.删除 my.cnf

rm -rf /etc/my.cnf
1

# 2. MySQL的Linux版安装

# 2.1 CentOS7下检查MySQL依赖

1. 检查/tmp临时目录权限（必不可少）

由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行 ：

chmod -R 777 /tmp
1

2. 安装前，检查依赖

rpm -qa|grep libaio
rpm -qa|grep net-tools
1
2

# 2.2 CentOS7下MySQL安装过程

image-20240224003107115

1. 将安装程序拷贝到/opt目录下

image-20240224005829309

在mysql的安装文件目录下执行：（必须按照顺序执行）

rpm -ivh mysql-community-common-8.3.0-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-plugins-8.3.0-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-8.3.0-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-8.3.0-1.el7.x86_64.rpm 
rpm -ivh mysql-community-icu-data-files-8.3.0-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.3.0-1.el7.x86_64.rpm
1
2
3
4
5
6
  • rpm是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。
  • -i, --install 安装软件包
  • -v, --verbose 提供更多的详细信息输出
  • -h, --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

image-20240224090219051

若存在mariadb-libs问题，则执行yum remove mysql-libs清除之前安装过的依赖即可

# 2.3 查看MySQL版本

mysql --version 
#或
mysqladmin --version
1
2
3

# 2.4 服务的初始化

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

mysqld --initialize --user=mysql
1

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

查看密码：

cat /var/log/mysqld.log
1

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

image-20240224010132939

# 2.5 启动MySQL，查看状态

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

# 2.6 查看MySQL服务是否自启动

systemctl list-unit-files|grep mysqld.service
1
  • 如不是enabled可以运行如下命令设置自启动
systemctl enable mysqld.service
1
  • 如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld.service
1

# 3. MySQL登录

# 3.1 首次登录

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

# 3.2 修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
1

# 3.3 防火墙端口设置

1. 确认网络

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

2.在远程机器上使用telnet命令保证端口号开放访问

2. 关闭防火墙或开放端口

方式一：关闭防火墙

  • CentOS6 ：
service iptables stop
1
  • CentOS7：
#开启防火墙
systemctl start firewalld.service
#查看防火墙状态
systemctl status firewalld.service
#关闭防火墙
systemctl stop firewalld.service
#设置开机启用防火墙 
systemctl enable firewalld.service 
#设置开机禁用防火墙 
systemctl disable firewalld.service
1
2
3
4
5
6
7
8
9
10

方式二：开放端口

  • 查看开放的端口号
firewall-cmd --list-all
1
  • 设置开放的端口号
firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent
1
2
  • 重启防火墙
firewall-cmd --reload
1

# 4. 开启远程登录

mysql5.x版本开启远程登录

# 适用于mysql5.7
use mysql;
select Host,User from user;
update user set host = '%' where user ='root';
flush privileges;
1
2
3
4
5

%是个 通配符 ，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%，表示所有IP都有连接权限。

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

在mysql8.0使用以上配置会出现连接报错：错误号码 2058，原因是因为在 mysql8.0以后版本 密码加密方法变了。

mysql8.x版本开启远程登录

# 适用于mysql8.0以上版本
use mysql;
select Host,User from user;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123'; 
flush privileges;
1
2
3
4
5
  • 指定用户：'root'@'%'表示对用户名为root、从任何主机连接(%代表任意主机)的用户进行操作。
  • 密码加密方式：IDENTIFIED WITH mysql_native_password指定使用mysql_native_password加密方式。这是因为MySQL 8.0引入了新的默认加密机制caching_sha2_password，但不是所有客户端都支持这种加密方式。mysql_native_password更兼容，尤其是在使用旧版客户端或第三方工具时。
  • 设置密码：BY 'abc123'将该用户的密码设置为abc123（此处修改后本地和远程都是这个密码）

image-20240224013219061

如果你想要创建一个新用户用于远程连接，可以按照以下操作

# mysql8.0创建一个新用户用于远程连接
create user 'remote_user'@'%' identified by 'abc123';
grant all privileges on *.* to 'remote_user'@'%' with grant option;  # 为该用户授予数据库的所有权限
flush privileges; # 刷新权限
1
2
3
4

# 5. 字符集的相关操作

# 5.1 修改步骤

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。网站开发人员在数据库

设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。从MySQL

8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

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

# 查看默认使用的字符集
show variables like 'character%';
# 或者
show variables like '%char%';
1
2
3
4

MySQL8.0中执行：

image-20240224035244714

MySQL5.7中执行：

image-20240224035419066

由于MySQL 5.7 默认的客户端和服务器都用了 latin1 ，不支持中文，所以保存中文会报错。示例如下：

image-20240224015939676

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

image-20240224020100132

操作2：修改字符集

vim /etc/my.cnf
1

在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：

character_set_server=utf8
1

image-20240224020424891

操作3：重新启动MySQL服务

systemctl restart mysqld
1

但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

# 5.2 已有库&表字符集的变更

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

image-20240224020601487

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

alter database dbtest1 character set 'utf8';
1

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

alter table t_emp convert to character set 'utf8';
1

测试插入数据：

image-20240224020904507

image-20240224020959569

注意

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

# 5.3 各级别的字符集

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

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

执行如下SQL语句：

show variables like 'character%'
1

image-20240224021254531

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

# 5.3.1 服务器级别

  • character_set_server ：服务器级别的字符集。

  • 我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量

    的值。比如我们可以在配置文件中这样写：

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

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

# 5.3.2 数据库级别

  • character_set_database ：当前数据库的字符集

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：default可以省略，不影响语义

# 创建数据库时 指定该数据库的字符集和比较规则
create database 数据库名
[[default] character set 字符集名称]
[[default] collate 比较规则名称];
# 修改数据库时 指定该数据库的字符集和比较规则
alter database 数据库名
[[default] character set 字符集名称]
[[default] collate 比较规则名称];
1
2
3
4
5
6
7
8

# 5.3.3 表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：（default可以省略，不影响语义）

# 修改表时 指定该表库的字符集和比较规则
create table 表名 (列的信息)
[[default] character set 字符集名称]
[collate 比较规则名称]]
# 修改表时 指定该表的字符集和比较规则
alter table 表名
[[default] character set 字符集名称]
[collate 比较规则名称]
1
2
3
4
5
6
7
8

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

# 5.3.4 列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列

定义的时候可以指定该列的字符集和比较规则，语法如下：

# 创建表时 指定该表列的字符集和比较规则
create table 表名(
列名 字符串类型 [character set 字符集名称] [collate 比较规则名称],
其他列...
);
# 修改表时 指定该表列的字符集和比较规则
alter table 表名 modify 列名 字符串类型 [character set 字符集名称] [collate 比较规则名称];
1
2
3
4
5
6
7

提示

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

# 5.3.5 小结

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

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据

这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表 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)
1
2
3
4
5
6
7
8
9

首先列 col 使用的字符集是 gbk ，一个字符 '我' 在 gbk 中的编码为 0xCED2 ，占用两个字节，两个字

符的实际数据就占用4个字节。如果把该列的字符集修改为 utf8 的话，这两个字符就实际占用6个字节

# 5.4 字符集与比较规则(了解)

1. utf8 与 utf8mb4

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示

了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计

MySQL的设计者偷偷的定义了两个概念：

  • utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。
  • utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

对于大多数现代应用，推荐使用utf8mb4字符集和utf8mb4_unicode_ci或utf8mb4_0900_ai_ci比较规则。这样可以确保应用支持全范围的Unicode字符，同时在多语言环境下有良好的字符比较和排序支持。（_ci后缀表示大小写不敏感，_cs表示大小写敏感）

2. 比较规则

image-20240224060908072

如上图，MySQL版本一共支持41种字符集，其中的 Default collation 列表示这种字符集中一种默认

的比较规则，里面包含着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则

比较， utf8_spanish_ci 是以西班牙语的规则比较， utf8_general_ci 是一种通用的比较规则。

后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

image-20240224023242785

常用操作1：

#查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';
#查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';
1
2
3
4

常用操作2：

#查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';
#查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';
#查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
#修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
1
2
3
4
5
6
7
8

常用操作3：

#查看表的字符集
show create table employees;
#查看表的比较规则
show table status from atguigudb like 'employees';
#修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
1
2
3
4
5
6

image-20240224061451574

image-20240224061744995

# 5.5 请求到响应过程中字符集的变化

我们知道从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串,而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的,从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量,我们先把它们写出来看一下：

image-20240224063036416

这几个系统变量在我的计算机上的默认值如下（不同操作系统的默认值可能不同）：

image-20240224063141436

  • 将character_set_client设置为utf8，这是服务器在解释客户端发送的SQL语句时使用的字符集。
  • 将character_set_connection设置为utf8，这是服务器在进行字符集转换时使用的中间字符集。
  • 将character_set_results设置为utf8，这是服务器返回查询结果给客户端时使用的字符集。

image-20240224063331466

经验：开发中通常把character_set_client,character_set_connection,character_set_results这三个系统变量设置成和客户端使用的字符集一致的情况,这样减少了很多无谓的字符集转换。为了方便我们设置,MySQL提供了一条非常简便的语句：

# SET NAMES utf8命令的效果是临时的，它仅仅对当前数据库连接会话有效
SET NAMES 字符集名；
1
2

这一条语句产生的效果和我们执行这3条的效果是一样的：

SET character_set_client = 字符集名;
SET character_set_connection =字符集名;
SET character_set_results = 字符集名;
1
2
3

比方说我的客户端使用的是utf8 字符集，所以需要把这几个系统变量的值都设置为utf8：

mysql> SET NAMES utf8;
1

另外,如果你希望每次连接数据库时就把character_set_client、character_set_connection、character_set_results 这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set 的启动选项，比如在配置文件里可以这么写：vim /etc/my.cnf 进入mysql配置文件，添加[client]节，并指定default-character-set选项。

[client]
default-character-set=utf8
1
2

# 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系统如下：

image-20240224024855646

Linux系统如下：

image-20240224024926158

lower_case_table_names参数值的设置：Linux下默认为0，大小写敏感

  1. lower_case_table_names=0（默认在Unix/Linux）：表名的存储和比较是区分大小写的。这意味着，MyTable和mytable会被视为两个不同的表。
  2. lower_case_table_names=1（默认在Windows）：MySQL服务器将表名转换为小写存储在磁盘上，并且在比较表名时不区分大小写。这意味着，不管你在SQL语句中如何指定表名（MyTable、MYTABLE或mytable），MySQL都会认为它们是同一个表。这个设置在Windows上是默认的，因为Windows的文件系统是不区分大小写的。
  3. lower_case_table_names=2（推荐在MacOS）：表名的存储是按照创建时在SQL语句中指定的大小写格式存储的，但MySQL在比较表名时会将它们转换为小写。这意味着，如果你创建了一个名为MyTable的表，它会以MyTable的形式存储在磁盘上，但是当执行查询时，SELECT * FROM mytable;和SELECT * FROM MYTABLE;都会被正确地解析为对MyTable的查询。

笔记

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

1、数据库名、表名、表的别名、变量名是严格区分大小写的；

2、关键字、函数名称在 SQL 中不区分大小写；

3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

MySQL在Windows的环境下全部不区分大小写

重要的注意事项：

  • 改变lower_case_table_names设置：如果你想改变这个设置，推荐在安装MySQL之前就设定好，因为更改这个参数需要你重新初始化数据库（也就是说，你需要删除所有现有的数据库，然后重新创建它们），这可能会导致数据丢失。
  • 跨平台数据迁移：当你从一个操作系统迁移到另一个，比如从Windows（通常lower_case_table_names=1）迁移到Linux（默认lower_case_table_names=0），需要特别注意这个设置，因为它可能会影响到数据迁移的过程和结果。

# 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服务
    1
    2
    3
    4

# 6.3 SQL编写建议

如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议

  1. 关键字和函数名称全部大写；
  2. 数据库名、表名、表别名、字段名、字段别名等全部小写；
  3. SQL 语句必须以分号结尾。

数据库名、表名和字段名在 Linux MySQL 环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。

虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

公众号封面

编辑此页 (opens new window)
上次更新: 2024/12/28, 18:32:08
第18章_MySQL8其它特性
第02章_MySQL的数据目录

← 第02章_MySQL的数据目录→

Theme by Vdoing | Copyright © 2019-2025 程序员scholar