在本地安装mysql,以mariadb为例。

所有命令都需要在root下面执行or使用sudo

本文基于系统 CentOS 8

1.安装mariadb开发包

在 CentOS8 下用如下命令

1
2
3
4
yum -y update
yum install -y mariadb
yum install -y mariadb-server
yum install -y mariadb-devel

在 Ubuntu 22.04 系统上用如下命令

1
2
3
sudo apt update
sudo apt install -y mariadb-server mariadb-client
sudo apt install -y libmysqlclient-dev # MySQL的C语言开发包

注意,后文没有强调处的操作全都基于 CentOS8,但在 Ubuntu 上的配置操作应类似。可供参考

2.修改配置文件中的编码

为了保证对中文的支持,我们需要修改几个配置文件。他们都在如下目录中

1
2
$ ls /etc/my.cnf.d/
auth_gssapi.cnf client.cnf enable_encryption.preset mariadb-server.cnf mysql-clients.cnf

需要修改的是如下3个,修改之前,建议使用cp命令进行备份,避免修改错了无法复原。

1
2
3
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/mariadb-server.cnf

我的已经是修改好的了,需要修改的配置看中文注释就ok

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ cat /etc/my.cnf.d/client.cnf
#
## These two groups are read by the client library
## Use it for options that affect all clients, but not the server
#


[client]
## 新增下边一行配置,设置客户端默认字符集为utf8
default-character-set = utf8

## This group is not read by mysql client library,
## If you use the same .cnf file for MySQL and MariaDB,
## use it for MariaDB-only client options
[client-mariadb]
1
2
3
4
5
6
7
8
9
10
11
$ cat /etc/my.cnf.d/mysql-clients.cnf
#
## These groups are read by MariaDB command-line tools
## Use it for options that affect only one utility
#

[mysql]
## 新增字符配置
default-character-set = utf8

[mysql_upgrade]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$ cat /etc/my.cnf.d/mariadb-server.cnf
#
## These groups are read by MariaDB server.
## Use it for options that only the server (but not clients) should see
#
## See the examples of server my.cnf files in /usr/share/mysql/
#

## this is read by the standalone daemon and embedded servers
[server]

## this is only for the mysqld standalone daemon
## Settings user and group are ignored when systemd is used.
## If you need to run mysqld under a different user or group,
## customize your systemd unit file for mysqld/mariadb according to the
## instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
## 新增以下字符集的配置
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
sql-mode = TRADITIONAL

3.测试

3.1 启动/关闭

1
systemctl start mariadb

看看状态

1
systemctl status mariadb

显示如下,那就是正常运行中的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
● mariadb.service - MariaDB 10.3 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since Tue 2023-04-04 14:10:00 CST; 2 days ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 633453 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
Process: 633289 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.service (code=exited, status=0/SUCCESS)
Process: 633265 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Main PID: 633422 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 31 (limit: 75691)
Memory: 67.4M
CGroup: /system.slice/mariadb.service
└─633422 /usr/libexec/mysqld --basedir=/usr

image-20230406190224737

用ps命令也能看到相关的进程

1
2
3
4
$ ps jax | grep mysql
1 633422 633422 633422 ? -1 Ssl 27 4:10 /usr/libexec/mysqld --basedir=/usr
625302 634104 634104 625302 pts/48 634104 S+ 1000 0:00 mysql -uroot
642521 642555 642554 642521 pts/51 642554 S+ 1000 0:00 grep --color=auto mysql

如果想关闭,使用如下命令即可

1
systemctl stop mariadb

使用如下命令进行开机自启的配置

1
systemctl enable mariadb

执行效果如下

1
2
3
4
$ systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

取消开机自启,将enable改成disable就可以了

1
systemctl disable mariadb

3.2 进入mysql命令行

因为我们没有设置root用户的密码,所以不需要指定密码就能进入数据库

1
mysql -uroot

会显示出如下信息,包括mariadb的版本

1
2
3
4
5
6
7
8
9
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

3.3 看看字符编码是否修改成功

输入如下命令 (记得要输入末尾的分号)

1
show variables like "%chara%";

看看是否都配置为了utf-8,如下图,除了filesystem是二进制,其他都是utf8,那就是ok的;

编码集修改之前的是这样的,有一部分的编码是latin1。

image-20230407165807546

修改后,除了filesystem外其他编码集都成功修改为了utf8

Snipaste_2023-04-04_14-12-41

到这里,就ok菈!

3.4 端口

mysql服务的默认端口为3306:::代表它是采用ipv6协议在本地进行访问的。但这不影响我们的使用;

image-20230408142003050

在Ubuntu系统上该命令执行结果如下

1
2
3
4
❯ netstat -nltp | grep 3306
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN -

可能有人会建议你修改mysql的默认端口,以避免被攻击。个人认为:这完全没有必要!一台主机的可用端口也就65535个,真要攻击你,写个脚本遍历完毕你主机上的所有端口都用不了1秒种的,很容易就能识别出哪一个端口的服务是什么(不同服务协议都有不同的握手手段,挨个去试就能知道)。

而且,在绝大部分时候,我们都不需要把自己的mysql服务端口暴露在公网上进行远程连接。

4.彻底卸载

有的时候,一个环境中已有mysql,但不是我们需要的版本。就需要将其删除后重新安装新的mysql。

1
2
rpm -qa | grep mysql
rpm -qa | grep mariadb

使用这两个命令,可以查看当前系统中安装的mysql(mariadb)的包

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@1c2261732150:~]# rpm -qa | grep mysql
[root@1c2261732150:~]# rpm -qa | grep mariadb
mariadb-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-server-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-common-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-errmsg-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-connector-c-3.1.11-2.el8_3.x86_64
mariadb-backup-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-server-utils-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-devel-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-connector-c-config-3.1.11-2.el8_3.noarch
mariadb-gssapi-server-10.3.28-1.module_el8.3.0+757+d382997d.x86_64
mariadb-connector-c-devel-3.1.11-2.el8_3.x86_64

可以看到,我的系统中安装的是mariadb(而不是mysql)

这时候就可以用一条命令,快速进行批量卸载

1
rpm -qa | grep mariadb | xargs yum -y remove

这条命令中xargs的作用就是把前面这个grep命令的结果,按行喂给yum进行删除。

删除之后,再次执行。

1
rpm -qa | grep mariadb

如果没有软件,那就是卸载完毕了!👌👌

1
2
[root@1c2261732150:~/package]# rpm -qa | grep mariadb
[root@1c2261732150:~/package]#

注意,卸载mysql并不会连带删除数据文件

5.yum安装特定版本的MySQL(未成功)

以下操作基于CentOS8

5.1 查看系统版本

1
2
$ cat /etc/system-release
CentOS Linux release 8.5.2111

当前我使用的系统为CentOS 8.5,使用yum安装的mariadb的版本比较高

1
2
## mysql --version
mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1

假设我需要一个老版本的mysql,就需要去安装特定的rpm版本

5.2 获取yum源

1
http://repo.mysql.com/

找到el8后缀的包,el指代的就是CentOS

1
mysql80-community-release-el8-3.noarch.rpm

如上,就是一个mysql 8.0版本的CentOS8的rpm源。

在很多教程中,使用的都是5.7版本的mysql,可惜CentOS8已经不支持这个旧版本了(找不到对应的mysql57的包)

5.3 安装yum源

可以使用windows下载了rpm文件后,上传到服务器中。或者用如下命令在linux内部下载。

如下目录是我们系统源的路径,一般情况下,内部不会带有和mysql相关的源。

1
ls -al /etc/yum.repos.d/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
total 92
drwxr-xr-x 1 root root 4096 Apr 6 19:14 .
drwxr-xr-x 1 root root 4096 Apr 6 19:14 ..
-rw-r--r-- 1 root root 2590 Feb 24 08:08 CentOS-Base.repo
-rw-r--r-- 1 root root 966 Feb 24 08:00 CentOS-Linux-AppStream.repo
-rw-r--r-- 1 root root 710 Feb 24 07:56 CentOS-Linux-BaseOS.repo
-rw-r--r-- 1 root root 1136 Feb 24 07:56 CentOS-Linux-ContinuousRelease.repo
-rw-r--r-- 1 root root 318 Feb 24 07:56 CentOS-Linux-Debuginfo.repo
-rw-r--r-- 1 root root 738 Feb 24 07:56 CentOS-Linux-Devel.repo
-rw-r--r-- 1 root root 710 Feb 24 07:56 CentOS-Linux-Extras.repo
-rw-r--r-- 1 root root 725 Feb 24 07:56 CentOS-Linux-FastTrack.repo
-rw-r--r-- 1 root root 746 Feb 24 07:56 CentOS-Linux-HighAvailability.repo
-rw-r--r-- 1 root root 693 Feb 24 07:56 CentOS-Linux-Media.repo
-rw-r--r-- 1 root root 712 Feb 24 07:56 CentOS-Linux-Plus.repo
-rw-r--r-- 1 root root 730 Feb 24 07:56 CentOS-Linux-PowerTools.repo
-rw-r--r-- 1 root root 1124 Feb 24 07:56 CentOS-Linux-Sources.repo
-rw-r--r-- 1 root root 0 Feb 24 08:05 epel-8.repo
-rw-r--r-- 1 root root 1698 Oct 4 2022 epel-modular.repo
-rw-r--r-- 1 root root 1332 Oct 4 2022 epel.repo
-rw-r--r-- 1 root root 1417 Jun 8 2021 epel.repo.rpmnew
-rw-r--r-- 1 root root 2318 Feb 24 08:08 epel.repo.rpmsave
-rw-r--r-- 1 root root 1797 Oct 4 2022 epel-testing-modular.repo
-rw-r--r-- 1 root root 1431 Oct 4 2022 epel-testing.repo
-rw-r--r-- 1 root root 164 Feb 27 12:21 vscode.repo

将数据传到云服务器后,使用如下命令,进行源安装

1
rpm -Uvh mysql80-community-release-el8-3.noarch.rpm

输出如下

1
2
3
4
5
6
warning: mysql80-community-release-el8-3.noarch.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Verifying... ################################# [100%]
warning: Unable to get systemd shutdown inhibition lock: Unit systemd-logind.service is masked.
Preparing... ################################# [100%]
Updating / installing...
1:mysql80-community-release-el8-3 ################################# [100%]

安装完毕,再次查看yum源路径,可以看到比原来多了两个mysql的包

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
## ls -al /etc/yum.repos.d/
total 100
drwxr-xr-x 1 root root 4096 Apr 7 16:12 .
drwxr-xr-x 1 root root 4096 Apr 6 19:14 ..
-rw-r--r-- 1 root root 2590 Feb 24 08:08 CentOS-Base.repo
-rw-r--r-- 1 root root 966 Feb 24 08:00 CentOS-Linux-AppStream.repo
-rw-r--r-- 1 root root 710 Feb 24 07:56 CentOS-Linux-BaseOS.repo
-rw-r--r-- 1 root root 1136 Feb 24 07:56 CentOS-Linux-ContinuousRelease.repo
-rw-r--r-- 1 root root 318 Feb 24 07:56 CentOS-Linux-Debuginfo.repo
-rw-r--r-- 1 root root 738 Feb 24 07:56 CentOS-Linux-Devel.repo
-rw-r--r-- 1 root root 710 Feb 24 07:56 CentOS-Linux-Extras.repo
-rw-r--r-- 1 root root 725 Feb 24 07:56 CentOS-Linux-FastTrack.repo
-rw-r--r-- 1 root root 746 Feb 24 07:56 CentOS-Linux-HighAvailability.repo
-rw-r--r-- 1 root root 693 Feb 24 07:56 CentOS-Linux-Media.repo
-rw-r--r-- 1 root root 712 Feb 24 07:56 CentOS-Linux-Plus.repo
-rw-r--r-- 1 root root 730 Feb 24 07:56 CentOS-Linux-PowerTools.repo
-rw-r--r-- 1 root root 1124 Feb 24 07:56 CentOS-Linux-Sources.repo
-rw-r--r-- 1 root root 0 Feb 24 08:05 epel-8.repo
-rw-r--r-- 1 root root 1698 Oct 4 2022 epel-modular.repo
-rw-r--r-- 1 root root 1332 Oct 4 2022 epel.repo
-rw-r--r-- 1 root root 1417 Jun 8 2021 epel.repo.rpmnew
-rw-r--r-- 1 root root 2318 Feb 24 08:08 epel.repo.rpmsave
-rw-r--r-- 1 root root 1797 Oct 4 2022 epel-testing-modular.repo
-rw-r--r-- 1 root root 1431 Oct 4 2022 epel-testing.repo
-rw-r--r-- 1 root root 1265 Jan 10 2022 mysql-community.repo
-rw-r--r-- 1 root root 1321 Jan 10 2022 mysql-community-source.repo
-rw-r--r-- 1 root root 164 Feb 27 12:21 vscode.repo

这时候,yum源就已经安装成功了

1
yum list | grep mysql

执行后,可以看到,列在最前面的就是el8-3,即为刚刚我们添加的的yum源中的mysql版本

1
2
3
4
5
6
Repository AppStream is listed more than once in the configuration
Repository extras is listed more than once in the configuration
mysql80-community-release.noarch el8-3 @System
anope-mysql.x86_64 2.0.12-1.el8 epel
ansible-collection-community-mysql.noarch 3.5.1-1.el8 epel
apr-util-mysql.x86_64 1.6.1-6.el8 AppStream

image-20230407161955819

5.4 卸载旧版本的

如果需要安装新版本的,那就需要依照第四步的操作,彻底卸载旧版本的mysql

5.5 安装指定版本

此时我们指定mysql进行安装,就肯定能安装到刚刚添加的mysql8.0版本

1
2
3
yum install -y mysql
yum install -y mysql-server
yum install -y mysql-devel

image-20230407162538787

可以看到,版本就是8.0的,不再是之前安装的Ver 15.1

1
2
$ mysql --version
mysql Ver 8.0.26 for Linux on x86_64 (Source distribution)

共安装了下面的这些包

1
2
3
4
5
6
7
8
9
10
## rpm -qa | grep mysql
mysql-8.0.26-1.module_el8.4.0+915+de215114.x86_64
mysql-errmsg-8.0.26-1.module_el8.4.0+915+de215114.x86_64
mysql-server-8.0.26-1.module_el8.4.0+915+de215114.x86_64
mysql-devel-8.0.26-1.module_el8.4.0+915+de215114.x86_64
mysql-common-8.0.26-1.module_el8.4.0+915+de215114.x86_64
mysql80-community-release-el8-4.noarch
mysql-libs-8.0.26-1.module_el8.4.0+915+de215114.x86_64
## rpm -qa | grep mariadb
mariadb-connector-c-config-3.1.11-2.el8_3.noarch

5.6 启动报错(未解决)

但是这次启动的时候,却遇到了下面的报错

1
2
3
## systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

查看报错信息也看不出个所以然,看上去MySQL5.7版本在自动升级为MySQL8?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
Active: failed (Result: exit-code) since Fri 2023-04-07 16:39:51 CST; 7s ago
Process: 648073 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
Process: 648059 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=1/FAILURE)
Process: 648023 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
Process: 647999 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Main PID: 648059 (code=exited, status=1/FAILURE)
Status: "Data Dictionary upgrade from MySQL 5.7 in progress"

Apr 07 16:39:50 1c2261732150 systemd[1]: Starting MySQL 8.0 database server...
Apr 07 16:39:51 1c2261732150 systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
Apr 07 16:39:51 1c2261732150 systemd[1]: mysqld.service: Failed with result 'exit-code'.
Apr 07 16:39:51 1c2261732150 systemd[1]: Failed to start MySQL 8.0 database server.

百度了一下,这个问题是因为mysql的路径权限不足

https://blog.csdn.net/Shockang/article/details/118061120

https://blog.csdn.net/weixin_44198965/article/details/104145043

但是我修改了之后还是没有用,百度了另外几个操作,都没有办法正常启动mysqld,那能咋办,放弃了。用回最开始安装的mariadb了😥

6.docker安装特定版本MySQL

虽然上面第五点的yum安装方式我在本地没有测试通过,但我们用docker安装特定版本MySQL是可以的。

6.1 安装docker

参考本站教程:【Docker】deepin/centos安装docker | 慕雪的寒舍

6.2 docker安装MySQL容器

下面是一个docker安装MySQL5.7版本的命令,你只需要修改冒号左边的宿主机文件路径即可。

1
2
3
4
5
6
7
8
sudo docker run -d \
-p 13306:3306 \
-v /home/wsl/docker/mysql5.7/log:/var/log/mysql \
-v /home/wsl/docker/mysql5.7/data:/var/lib/mysql \
-v /home/wsl/docker/mysql5.7/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql57 \
mysql:5.7

安装完毕该容器后,可以用如下命令进入docker内MySQL的命令行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
❯ sudo docker exec -it mysql57 mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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>

6.3 修改字符集和终端配置

问题说明

docker安装的MySQL默认字符集都是latin1而且命令行中文输入都不允许!我们需要将MySQL的字符集修改为utf8,同时还需要修改MySQL所在docker环境内终端的字符集,否则中文输入不被允许。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查看数据库字符集
mysql> show variables like "%chara%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

中文输入不允许的情况如下,输入法无法输入中文,即便复制粘贴的中文也会消失不见,中文内容会消失。

1
mysql> insert into stu values (4,'');

image.png

修改MySQL配置

因为我们映射了配置文件目录,所以修改会比较方便。在/home/wsl/docker/mysql5.7/conf中创建一个my.cnf,写入如下内容

1
2
3
4
5
[client]
default_character_set=utf8
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

随后重启MySQL容器,应用配置文件的更改

1
sudo docker restart mysql57

重新进入MySQL命令行查看字符集,可以看到结果不同了,此时字符集已被成功修改。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like "%chara%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

修改docker容器终端字符集

参考:Docker版本Mysql容器中不支持输入中文解决方法 - YOLO_BLOG - 博客园

随后是修改容器终端的字符集,进入docker容器内的的bash终端。

1
sudo docker exec -it mysql57 /bin/bash

使用locale命令查看当前终端内的字符集设置,会发现都是POSIX字符集,这个字符集很老,不支持中文键入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
bash-4.2# locale
LANG=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=

使用locale -a查看终端支持的所有字符集,会发现有非常之多。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
bash-4.2# locale -a
C
C.utf8
POSIX
en_AG
en_AG.utf8
en_AU
en_AU.iso88591
en_AU.utf8
en_BW
en_BW.iso88591
en_BW.utf8
en_CA
en_CA.iso88591
en_CA.utf8
en_DK
en_DK.iso88591
en_DK.utf8
en_GB
en_GB.iso88591
en_GB.iso885915
en_GB.utf8
en_HK
en_HK.iso88591
en_HK.utf8
en_IE
en_IE.iso88591
en_IE.iso885915@euro
en_IE.utf8
en_IE@euro
en_IN
en_IN.utf8
en_NG
en_NG.utf8
en_NZ
en_NZ.iso88591
en_NZ.utf8
en_PH
en_PH.iso88591
en_PH.utf8
en_SG
en_SG.iso88591
en_SG.utf8
en_US
en_US.iso88591
en_US.iso885915
en_US.utf8
en_ZA
en_ZA.iso88591
en_ZA.utf8
en_ZM
en_ZM.utf8
en_ZW
en_ZW.iso88591
en_ZW.utf8

我们选择C.utf8字符集即可支持中文键入,使用如下命令修改

1
echo "export LANG=C.UTF-8" >>/etc/profile && source /etc/profile

修改后重新查看终端配置,确认修改字符集成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
bash-4.2# locale
LANG=C.UTF-8
LC_CTYPE="C.UTF-8"
LC_NUMERIC="C.UTF-8"
LC_TIME="C.UTF-8"
LC_COLLATE="C.UTF-8"
LC_MONETARY="C.UTF-8"
LC_MESSAGES="C.UTF-8"
LC_PAPER="C.UTF-8"
LC_NAME="C.UTF-8"
LC_ADDRESS="C.UTF-8"
LC_TELEPHONE="C.UTF-8"
LC_MEASUREMENT="C.UTF-8"
LC_IDENTIFICATION="C.UTF-8"
LC_ALL=

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

重新进入MySQL终端,发现此时已经可以键入中文了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
bash-4.2# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> use test;
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> insert into stu values (4,'小明');
ERROR 1366 (HY000): Incorrect string value: '\xE5\xB0\x8F\xE6\x98\x8E' for column 'name' at row 1

但是这里弹了一个ERROR,说我们的string变量不正确,不支持插入。

这是因为这个test数据库和stu表都是在修改MySQL字符集之前创建的,它们的字符集还没有被修改,依旧是latin1字符集!

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)

使用如下命令设置这个数据库test和表stu的字符集为uft8;

1
2
ALTER DATABASE 数据库名字 CHARACTER SET utf8mb4;
ALTER TABLE 表名字 CONVERT TO CHARACTER SET utf8mb4;

效果如下

1
2
3
4
5
6
mysql> ALTER DATABASE test CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE stu CONVERT TO CHARACTER SET utf8mb4;
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0

此时就能正常插入中文了

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> insert into stu values (4,'小明');
Query OK, 1 row affected (0.03 sec)

mysql> select * from stu;
+------+--------+
| id | name |
+------+--------+
| 1 | 1235 |
| 2 | 125 |
| 3 | |
| 4 | 小明 |
+------+--------+
4 rows in set (0.00 sec)

The end

本文讲述了安装mariadb和docker安装MySQL数据库以及字符集修改的方式,希望对你有帮助。