技巧

客户端工具mycli

使用好多年MySQL其实最重要的还是客户端工具，图形化不是王者，使用命令行才是王者。此工具就是MySQL客户端工具的王者，默认支持SQL自动补全，还很轻便。

参考：

mycli | MySQL 技术论坛

连接MySQL

1
mycli -u <用户名> -h <主机> -P <端口号>

用户管理

重置密码(忘记密码)

以下操作在Arch Linux 环境，数据库为10.5.9-MariaDB，经过2小时的搜索资料和实践，才有以下总结

首先，我们停掉MariaDB服务：

1
systemctl stop mariadb.service  #停止MariaDB

以安全模式启动MySQL：

1
sudo mysqld_safe --skip-grant-tables --skip-networking &        #--skip-networking；避免远程无密码登录 MariaDB，后面的&的作用是启动安全模式后能继续编辑命令窗口

这样我们就可以直接用root登录，无需密码：

1
mysql -u root -p

特别是此处的操作，不能和Mysql数据库的改密码命令一样，不然会出现ERROR 1348 (HY000): Column 'Password' is not updatable

1
2
3
4
5
6
7
mysql> flush privileges;    #必须执行此操作才可以进行以下
mysql> use mysql;  
mysql> set password for 'root'@'localhost'=password('123456');
#以下注释的两个适用于Mysql重置密码操作 
#update mysql.user set authentication_string=password('123456') where host='localhost' and user='root';
# ALTER USER 'root'@'localhost' identified by '123456';
mysql> flush privileges;

重设完毕后，我们退出，然后启动 MariaDB 服务：

1
mysql > quit

重启服务：

1
systemctl restart mariadb.service #重启MariaDB

接下来直接登录使用设置的密码123456即可，实际的运维当中不建议使用弱密码。

添加用户

localhost:可使用“%”（任何主机）、“localhost”（本地）、指定IP，但在最新版的MariaDB上发现使用“%”，不能使用本地主机登录，只能使用本地主机IP地址进行登录；

参考：

博客园:MySQL修改用户权限

CSDN:MariaDB用户访问权限设置

1
2
3
4
#创建用户只允许本地连接
create user '新用户名'@'localhost' identified by '密码';
#创建用户，允许所有ip连接（用通配符%表示）
create user '新用户名'@'%' identified by '密码';

设置用户授权

1
2
3
4
5
6
7
#只设置权限，不修改密码
grant all privileges on 数据库名.表名 to '新用户名'@'指定ip';
#修改权限同时修改密码
grant all privileges on 数据库名.表名 to '新用户名'@'指定ip' identified by '新用户密码' ;

#允许访问所有数据库"*"下的所有表"*" [同时修改密码]
grant all privileges on *.* to '新用户名'@'指定ip' [identified by '新用户密码'] ;

设置用户操作权限

1
2
3
4
5
6
7
8
9
#设置用户拥有所有权限也就是管理员
grant all privileges on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
#拥有查询权限
grant select on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
#其它操作权限说明,select查询 insert插入 delete删除 update修改
#设置用户拥有查询插入的权限
grant select,insert on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
#取消用户查询的查询权限
REVOKE select ON what FROM '新用户名';

在MySQL 8.0中，使用IDENTIFIED BY来设置用户的密码已经不再支持WITH GRANT OPTION选项

1
2
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'fuding';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

修改后刷新权限

1
2
# 这个每次都要执行最好
FLUSH PRIVILEGES;

查看修改后的用户权限

1
show grants for 'fuding'@'%';		#如果修改新用户权限成功，显示一大波允许的权限，类似：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `fuding`@`%`

删除用户

1
DROP USER username@localhost;

允许Mysql用户外部访问

  1. 登进MySQL之后。

  2. 输入以下语句，进入mysql库：

    1
    use mysql

  3. 更新域属性，’%’表示允许外部访问：

    注意MariaDB最新版本，“%”不代表本地主机可登录。

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

  4. 执行以上语句之后再执行：

    1
    FLUSH PRIVILEGES;

基础知识

索引

参考：

知乎-数据库索引，你要了解的都在这里！

CHAR、VARCHAR、TEXT、BLOB总结

以下数据库引擎采用InnoDB

参考：

知乎：CHAR与VARCHAR详解

博客园：MySQL中的char与varchar详解

CSDN：MySQL_TEXT与BLOB字段类型的区别

博客园：MySQL中TEXT与BLOB字段类型的区别

CHAR、VARCHAR类型区别：

CHAR VARCHAR
定长 不定长
存取速度效率高，一般用于固定长度的表单提交数据存储（身份证、手机号、姓名） 存取速度效率偏低
CHAR(M)定义的列的长度为固定的，M取值可以为0～255个字符 4.1以下的版本中的最大长度限制为255
MySQL5.0以上的版本中varchar数据类型的行长度支持到了0-65535字节
一个英文字符占用1个字节
一个汉字占用两个字节		 字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845
只限制列长度最大255 varchar最长是64k，整个行的长度不可超过这个储存空间，1024*64字节
  • 定长与不定长（定义一个Char(10)和一个Varchar(10)，同时存入‘abcd’）
    • Char(10)，所占的长度依然为10，除了字符‘ABCD’外，CHAR类型的要用trim()去掉多余的空格，后面跟六个空格。
    • Varchar(10)，长度变为4了，取数据的时候，VARCHAR类型是不需要用trim()去掉多余的空格的。

TEXT、BLOB类型：

TEXT BLOB
只支持文本 支持图片，转换成二进制数据
INYTEXT、TEXT、MEDIUMTEXT、LONGTEXT四种类型 TinyBlob 最大 255个字符
Blob 最大 65K
MediumBlob 最大 16M
LongBlob 最大 4G
EXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较 BLOB列没有字符集，并且排序和比较基于列值字节的数值值
存储或检索过程中，不存在大小写转换 存储或检索过程中，不存在大小写转换
保存或检索BLOB和TEXT列的值时不删除尾部空格 保存或检索BLOB和TEXT列的值时不删除尾部空格
不能有默认值 不能有默认值

注意：

  • 实际使用中根据需要存入的数据大小定义不同的BLOB类型，除了类型对后面存取文件大小有限制，还要修改mysql的配置文件。Windows、linux基本一样通过修改文件my.ini或my.cnf文件，在文件中增加 max_allowed_packet=1M（就是最大1M）
    需要注意的是：如果你存储的文件过大，数据库的性能会下降很多。

  • 运行在非严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。

INT类型总结

参考：

博客园：MySQL_bigint(20) 是什么意思？

类型（默认大小） 存储空间 M默认值(最大显示宽度) 数据大小
tinyint(M) 1 tinyint(4) 有符号的范围：-128 到 127，无符号的范围： 0 到 255 的整型数据。
smallint(M) 2 smallint(6) 有符号的范围：-2^15 (-32,768) 到 2^15-1 (32,767) 的整型数据，无符号的范围：-32768 到 32767 【3万左右】
mediumint(M) 3 mediumint(9) 有符号的范围：-8388608到8388607，无符号的范围：0到16777215的整型数据。【1600万左右】
int(M) 4 int(11) -2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647) 的整型数据。【21亿左右】
bigint(M) 8 bigint(20) -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据。【远大于21亿左右】
  • M：表示最大显示宽度，建表若选择zerofill，会在数字前面补0，没设置zerofill，直接显示数字，不会影响储存空间大小，不设置没用上就会节省空间。
  • 储存空间：储存需要占多少字节。
  • 显示宽度和数据类型：
    • 如果插入了小于指定的宽度时会由空格填充，显示宽度只是指明Mysql最大可能显示的数字个数；
    • 如果插入了大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然可以插入，而且能够显示出来。

枚举ENUM总结

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

其所需要的存储空间由定义ENUM类型时指定的成员个数决定。

参考：

腾讯云：【mysql】ENUM类型

CSDN:关于 mysql 枚举类型(enum) 的说明

文本字符串类型 长度 长度范围 占用的存储空间
ENUM L 1 <= L <= 65535 1或2个字节
  • 当ENUM类型包含1～255个成员时，需要1个字节的存储空间；
  • 当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。
  • ENUM类型的成员个数的上限为65535个。
  • 含有类型索引下标，即定义元素就像数组那样，可以对其元素进行索引（INT）查询,但不同于数组索引，ENUM是从1开始的。

注意：

  • ENUM()内部定义元素，不能重复定义;
  • ENUM()内部定义元素，会忽略大小写；
  • 不能插入定义元素以外的数据；
  • 没有限制非空的情况下，可以添加null值# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的

多表查询总结

总结一下多表查询的内连接（INNER JOIN）、外连接的左右连接（LEFT JOIN、RIGHT JOIN）之间的区别，以及全连接（FULL JOIN），联合查询（UNION JOIN）。

参考：

腾讯云社区：Mysql中外连接,内连接,左连接,右连接的区别

博客园：MySQL多表创建关联及操作

内连接和外连接关系图：

连接查询的关系

连接查询之间的关联

首先建立两张表：

  • tableA

表A

  • tableB

表B

内连接：

相对而言，隐式连接好理解好书写，语法简单，担心的点较少。但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显（已实验）。

  • 显式内连接
1
select * from tableb INNER JOIN tablea on tablea.aid=tableb.bid;

显式内连接结果

  • 隐式内连接
1
select * from tableb,tablea where aid = bid;

隐式内连接结果

外连接：

  • 左连接

    只查询出left join左边表的完整数据，如果left join右边表没有对应的数据就会空出来。如果右边有有左边没右边不显示，但左边一定会一条不漏的显示。

1
select * from tablea LEFT JOIN tableb on tablea.aid=tableb.bid;

左连接查询结果

实验：

Left Join 实验

  • 右连接

    只查询出right join右边表的完整数据，right jsin左边表没有对应的数据就会空出来。如果左边有右边没左边不显示，但右边一定会一条不漏的显示。

1
select * from tablea RIGHT JOIN tableb on tablea.aid=tableb.bid;

右连查询结果

  • 全外连接

    全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。

    1
    select * from tablea FULL JOIN tableb on tablea.aid=tableb.bid;

    可理解为：

    1
    2
    3
    select  * f rom  tablea  left  join tableb on  tablea.aid=tableb.bid
    union	#去除重复
    select  * f rom  tablea  right  join tablea  on   tablea.aid=tableb.bid;

联合查询

相当于将两个表查询结果上下对齐显示，但下面的表的字段数一定要和上面表的字段数相等，即使下表的字段和上表的字段名称不同，查询结果只会显示为上表字段名的查询结果。

查询语句，默认union会“自动消除重复行”，即默认是distinct 【不同】如果想要将所有数据都显示（允许重复行），就使用union all。

可以使用order by对表进行排序，不一定按照上表下表顺序进行。

参考；

阿里云开发者社区：MySQL表连接（内连接、交叉连接、外连接、联合查询）

union实现全外连接：

1
2
3
4
5
select  * f rom  表1  left  join 表2 on  条件

union

select  * f rom  表1  right  join 表2  on  条件

Limit分页

菜鸟教程：MYSQL LIMIT 用法

1
select _column,_column from _table [where Clause] [limit N][offset M]
  • limit N : 返回 N 条记录
  • offset M : 跳过 M 条记录, 默认 M=0, 单独使用似乎不起作用
  • limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录

数据库配置

数据库库创建

做项目如果要搭建到公网那经常会使用此操作，由于学期末的实训使用c3p0连接池搭建了个音乐共享网站，查表操作都放在session中（这里可能是导致连接数过多的原因），于是作者专门网上专门查了一系列的数据库连接数的配置操作，可见这方面的知识对于运维工作还是很重要的。

参考：

mysql使用utf8mb4经验吐血总结

创建新的数据库，选用字符集utf8mb4 排序字符集utf8mb4_unicode_ci：

建立数据库

  • MySQL在 5.5.3 之后增加了 utf8mb4 字符编码，mb4即 most bytes 4。简单说 utf8mb4 是 utf8 的超集并完全兼容utf8，能够用四个字节存储更多的字符。也就是当你的数据库里要求能够存入这些表情或宽字符时，可以把字段定义为 utf8mb4
  • 常见排序字符集有两种：
    • utf8mb4_unicode_ci 是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
    • utf8mb4_general_ci 没有实现Unicode排序规则，在遇到某些特殊语言或字符是，排序结果可能不是所期望的。
    • utf8mb4_general_ci 在比较和排序的时候更快
    • utf8mb4_unicode_ci 在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。
  • 但目前来说，性能已经不是这个时代的瓶颈了，推荐是 utf8mb4_unicode_ci，将来 8.0 里也极有可能使用变为默认的规则。

最大连接数

  • 方式一（root用户登录进Mysql才可以执行）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#查看mysql连接进程列表(你会发现大部分都是睡眠状态)
show full processlist;

# 修改最大连接数
set GLOBAL max_connections = 200;

#查看设置的最大连接数，默认100左右（通常，mysql的最大连接数默认是100, 最大可以达到16384。（16384=2的14次方））
show variables like '%max_connections%';

# 查看已经使用的连接数
show global status like 'Max_used_connections';

#查看进程等待多少秒后清除连接
show global variables like 'wait_timeout';

#设置禁触休息多少秒后清除连接
set global wait_timeout=10000;

# 设置打断后多少秒清除连接
set global interactive_timeout=300;

  • 方式二（通过修改配置文件来修改mysql最大连接数(max_connections)）

修改Linux配置文件路径/etc/mysql/my.cnf

只要修改MySQL配置文件my.ini 或 my.cnf的参数max_connections，将其改为max_connections=1000，然后重启MySQL即可。

Mysql 5.7

此版本经常会遇到的问题，比如跑一个项目的时候提示SQL描述 的表名称不存在。

参考：

stackoverflow-ZhaoGang:MySQL in Docker on Ubuntu: Warning: World-writable config file is ignored

MySQL：MySQL 8.0 Reference Manual：

askubuntu-Déjà vu：MySQL, my.cnf and /etc/alternatives

查看数据库当前大小写是否敏感

Value表示1为大小写不敏感，0为大小写敏感。

1
2
3
4
5
6
7
8
9
# SHOW VARIABLES LIKE '%case%';
MySQL root@localhost:(none)> SHOW VARIABLES LIKE 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set
Time: 0.007s

改配置文件my.cnf

Linux系统一般情况下此文件都在/etc/my.cnf中配置，且这个文件是经常需要配置的。

添加以下代码：

1
2
[mysqld]
lower_case_table_names=1

重启数据库

如果你是Docker环境，my.cnf 可能会不起作用，可能是因为文件的权限设置问题，详细请查看：stackoverflow-ZhaoGang:MySQL in Docker on Ubuntu: Warning: World-writable config file is ignored。

数据库备份

Mysql 数据库备份有好几种方式

Mysqldump

这个方式只要是你安装了Mysql就可以备份任何已知Mysql数据库的数据

1
mysqldump --host=127.0.0.1 --port=3305 --user=root -p dailyfresh > ~/Desktop/OpenSource/Dailyfresh-B2C/Backend/dailyfresh.sql
  • host 主机地址
  • port 端口
  • user 用户名
  • -p 执行后提示输入密码
  • dailyfresh 数据库名
  • > 覆盖写入文件

问题

写这个方式的原因是使用Mysql workbench出现了一个报错：Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)

参考：

serverfault

这个问题直接导致备份数据库报告以上错误，解决方式如下，直接使用命令行执行mysqldump备份时候加上--column-statistics=0：

  • 方案一

    1
    mysqldump --column-statistics=0 --host=<server> --port=<port> --user=<user> --password=<password> > <dump_file_path>

  • 方案二

    找到 MySQL 配置文件, 比如 /etc/my.cnf, ~/.my.cnf, or directly to `/etc/mysql/mysql.cnf，加上下面的：

    1
    2
    [mysqldump]
    column-statistics=0