MySQL/MariaDB 教程

安装方法

CentOS使用包的方式安装最新MariaDB,CentOS安装client直接yum install mysql而不是client,而安装mysql则直接用yum install -y mysql mysql-server mysql-dev mysql-devel,CentOS7上已经用mariadb代替了mysql,这样子使用:

1
2
3
yum install mariadb-server mariadb-client mariadb-devel -y
systemctl start mariadb.service # 启动服务
systemctl enable mariadb.service # 开机启动

Ubuntu:

1
2
3
4
5
6
7
8
9
# 安装最新版本,需要先导入对应的镜像库https://downloads.mariadb.org/mariadb/repositories
sudo apt-get install mariadb-server mariadb-client libmariadbd-dev

# 如果是开发,还需要安装
sudo apt-get install libmariadb-client-lgpl-dev
sudo ln -s /usr/bin/mariadb_config /usr/bin/mysql_config

# 第一次登录使用
sudo mysql -u root

常用命令

增删改查

SQL文件操作

1
2
# 执行sql文件
mysql -uroot -pmysql --default-character-set=gbk jpkc_db < jpkc_db.sql # 这里可以执行编码格式

数据库操作

1
2
3
4
## 创建数据库,如果是gbk编码,分别用gbk、gbk_chinese_ci;
CREATE DATABASE 库名 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
TRUNCATE tablename # 清空数据表
DROP database_name # 删除数据库

数据表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
## 清空数据表
DELETE FROM 表名; # 这种方式比较慢,但是可以恢复
TRUNCATE TABLE 表名 # 这种方式很快,但不会产生二进制日志,无法回复数据

ALTER TABLE 表名 DROP FOREIGN KEY '外键名'; # 删除外键
ALTER TABLE 表名 ADD 字段名 属性 AFTER 字段名; # 给表添加字段
ALTER TABLE 表名 DROP COLUMN 字段名; # 给表删除字段
ALTER TABLE 表名 CHANGE COLUMN 列名 新的列名 属性; # 修改列属性
ALTER TABLE 表名 MODIFY COLUMN 列名 属性; # 除了不能修改列名以外,其他都和CHNAGE一样

CREATE INDEX name_idx ON `表名`(`列名`); # 给表添加索引
ALTER TABLE `表名` ADD UNIQUE `键名`(`列名1`, `列名2`);

# mariadb创建Json字段,VARCHAR或者BLOB都可以使用,不对格式做要求,如果要做要求也可以强制做,例如
CREATE TABLE products(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
attr VARCHAR(1024),
CHECK (JSON_VALID(attr)));

数据记录操作

查询
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
# 普通查询
SELECT * FROM table_A
SELECT * FROM ... BETWEEN value1 AND value2
SELECT * FROM ... NOT BETWEEN value1 AND value2
SELECT DISTINCT(field_1) FORM ... # 去重
# 分组查询
SELECT count(column_a) as count FROM table_A GROUP_BY coulumn_b

# 多表子查询
## 需要注意的是,子查询后面必须要AS一个别名
update table_1 as a, (select id from biao_2 where name='a') as b set a.title='xx' where a.id=b.id

# 随机读取数据库记录
SELECT * FROM table WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM table))) ORDER BY id LIMIT 1

# 分页功能,获取m开始的n条记录
SELECT * FROM table_name limit m, n

# 模糊查询/正则查找
SELECT * FROM table_name like '%abc_'; # 模糊查询,其中%贪婪匹配任意数量的任意字符,_匹配一个任意字符
SELECT * FROM table_name REGEXP '(.*?)wtf';

# 分组GROUP BY
SELECT * FROM table_name GROUP BY `field1`, `field2`; # 分组显示,有多少不同的field就会有多少条记录,而其他的字段则是随机选择一条记录显示,当然,如果对其他字段进行SUM等操作,那么就可以获取分类的SUM,十分有用

# Having子句,与WHERE不同,它可以和一些统计函数一起使用
SELECT name, SUM(money) FROM users GROUP BY name HAVING SUM(money)>23333 # 这一句就能查找出所拥有的资产综合大于23333的用户
SELECT * FROM virtuals WHERE ip in (SELECT ip FROM virtuals GROUP BY ip HAVING COUNT(ip)>1); # 可以统计所有有重复的数据

LIKE查询的特殊转义

1
2
3
4
/: //
': /'
": /"
\: \\\\ # 没错,右斜杠需要这样做
连表查询
1
2
3
4
5
# LEFT JOIN ... ON ...
## 会取出左表的全部记录,即使右表没有对应匹配的记录。用这种方式SELECT出来的数据,如果右表数据为空,那么会给NULL

# 内连接INNER JOIN ... ON ...(等于与直接用JOIN)
## 语法和LEFT JOIN其实是一样的,只不过右表没有匹配的记录的情况下,最终的结果就不会出现左表的那一条数据
修改/更新
1
2
3
4
5
6
7
8
9
10
11
12
13
14
## 更改某字段的值,特别需要注意的是,mysql和mariadb是没有update from的,sql server才有。更新的时候WHERE语句一定是在SET语句后面,而JOIN语句则是在SET语句前面
UPDATE 表名 SET 字段=新值,字段2=新值2 WHERE 条件;
UPDATE table_A, table_B SET table_A.a=table_B.a;

## 更新中也能使用CASE,例如
UPDATE `table` SET `field` = CASE
WHEN id = 1 THEN 2
END
WHERE id in (1,2,3);

# 更新的时候使用LEFT JOIN等语句
UPDATE `table`
LEFT JOIN ... ON ...
SET ...
删除
1
DELETE `deadline` FROM `deadline` LEFT JOIN `job` 	# 有LEFT JOIN情况时删除指定表的数据
插入
1
2
3
4
5
6
# 插入数据
INSERT INTO 表名(属性列表) VALUES(值列表)
# 忽略重复的记录
INSERT IGNORE INTO ...
# 包含子查询的插入INSERT INTO SELECT
SELECT INTO db_name(field1, field2) SELECT field1, field2 FROM db_name2

系统相关

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
# 更改密码
## 如果提示权限不足,可以先停止服务,然后这样启动service mysql start --skip-grant-tables
use mysql;
update user set password=PASSWORD('mysql') WHERE user="root";
update user set authentication_string=PASSWORD('mysql') WHERE user="root"; # MySQL5.7以后password字段改为了authentication_string字段
flush privileges;

# 查看用户权限
show grants for 用户名

# 打开远程登录权限,如果是CentOS7还需要打开防火墙firewall-cmd --add-port=3306/tcp
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "mysql";
flush privileges; 更新权限
select host, user from user; 查看更改

# 查找系统常用变量
show global variables like 'log_error'; # 查看是否开启以及日志文件路径
SET GLOBAL general_log = 'ON';

# 记录下所有的sql命令
SHOW VARIABLES LIKE "general_log%"; SET GLOBAL general_log = 'ON'; # 临时解决方法,数据库重启后失效
bin-log = /tmp/mysql.log # 能一直都开着

# 数据库编码
show variables like 'character%'; # 查看关于编码的几个变量
character_set_client # 客户端编码方式
character_set_connection # 建立连接使用的编码方式
character_set_database # 数据库的编码
character_set_results # 结果集的编码
character_set_server # 数据库服务器的编码

# 设置数据库不区分大小写,vim /etc/mysql/my.cnf,在[mysqld]后面添加这句话,然后重启
lower_case_table_names=1

# 查询数据库数据存放目录
show variables like '%datadir%';

# 查看所有的警告
show warnings

# 查看MySQL版本
select @@version

# 查看表的结构
show columns from 表名;

数据库维护

1
2
3
4
5
6
7
8
9
10
11
12
13
# 备份整个数据库
mysqldump -u... -p... -h... -A > all.sql
mysqldump -uroot -pmysql --databases -h127.0.0.1 abc | gzip > test.sql.1.gz # 压缩,只能在本地进行压缩

# 备份多个数据库
mysqldump -u... -p... -h... --databases data1 data2 > backup.sql

# 导入数据
mysql -uroot -pmysql db_name < test.sql
bunzip2 < db_filename.sql.bz2 | mysql -uroot -pmysql db_name

# 忘记密码时候'Access denied for user 'root'@'localhost'的时候,可以用这种方式修改root权限
sudo mysqld_safe --skip-grant-tables # 这条命令能够登录进去,然后可以执行设置密码的操作

帮助函数

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
# 字符串相关
left(str, length) # 字符串截取
right(str, length) # 字符串截取
substring(str, pos, len) # 字符串截取
concat(str1, str2) # 字符串相加

# 数字相关
FLOOR() # 取整
ROUND() # 四舍五入

# 时间相关
YEAR(datetime) # 获取年份
QUARTER(datetime) # 获取季度数
MONTH(datetime) # 获取月份
MONTHNAME(datetime) # 获取月份名字
MONTHNAME(datetime) # 获取星期名字(比如'Thursday')
WEEKDAY(datetime) # 获取星期索引
WEEK(date, first) # 获取当前是一周的第几天,first表示周几算一周的开始
DAYOFMONTH(datetime) # 获取日期(几号)
DAYOFYEAR(date) # 返回date在一年中的日数(1-366)
HOUR(datetime) # 获取小时数
MINUTE(datetime) # 获取分钟数
SECOND(datetime) # 获取秒数

# 统计相关
SUM(field_name)
COUNT(field_name)
SUM(case when field='wang' then 1 else 0 end) as sum_if
COUNT(IF(field='wang',1,NULL)) as count_if # 使用if做统计

# 逻辑相关
CASE
WHEN 'field' = 1 THEN 2
WHEN 'field' = 2 THEN 3
ELSE 'field' = 3 THEN 4
END;
## 或者
CASE field WHEN '1' THEN '2' WHEN '3' ELSE '4' END

IF(sex=1, '男', '女') # if条件语句
IF(sex=1 OR field='b', 1, NULL) # 复杂的

# 字符串处理
REPLACE(field_name, "search", "replace") # 将search替换为replace,正则搜索,例如UPDATE `table` SET `value` = REPLACE(`value`, 'abc', 'def')

# JSON相关函数
JSON_ARRAY([]) # 将数组转换为json格式
JSON_KEYS(field_name) # 获取json数据的所有key
JSON_EXTRACT(result,'$.id') # 获取json数据key=id的值

数据库优化

常见性能问题及优化

  • COUNT(*)优化: Innodb数据库中表的总行数并没有直接存储,而是每次都执行全表扫描,如果表太大简单的COUNT(*)则会非常耗时。这时候不妨选择某个字段添加一个辅助索引,依然会扫描全表,但是COUNT(*)的性能能提高很多。因为在使用主键或者唯一索引的时候,InnoDB会先把所有的行读到数据缓冲区,发生了多次IO,而使用了辅助索引以后,由于辅助索引保存的仅仅是index的值,虽然还是读了那么多行到缓冲区,但是数据量则大大减少,仅有一个字段,磁盘IO减少,所以性能提高了。
  • char和varchar: Char是定长类型,对于经常变更的数据,一般采用CHAR来进行存储,因为CHAR类型在变化的时候不容易产生碎片。VARCHAR是变长类型,它比CHAR更节省空间。
  • 使用ENUM枚举类型来代替字符串类型
  • LIKE查询优化: 如果是abc%型的like查询是能用到该字段的索引的,如果是前后都模糊搜索,那么最好是加一个有索引的字段进行筛选,例如时间

索引类型

聚簇索引(clustered index)

保存了每一样的所有数据,聚簇索引的选择方法如下:

1
2
3
1.如果表中定义了PRIMARY KEY,那么InnoDB就会使用它作为聚簇索引;
2.否则,如果没有定义PRIMARY KEY,InnoDB会选择第一个有NOT NULL约束的唯一索引作为PRIMARY KEY,然后InnoDB会使用它作为聚簇索引
3.如果表中没有定义PRIMARY KEY或者合适的唯一索引。InnoDB内部会在含有行ID值的合成列生成隐藏的聚簇索引。这些行使用InnoDB赋予这些表的ID进行排序。行ID是6个字节的字段,且作为新行单一地自增。因此,根据行ID排序的行数据在物理上是根据插入的顺序进行排序

辅助索引(secondary index)

聚簇索引以外的就是辅助索引,辅助索引的每一行记录都包含每一行的主键列,辅助索引指向主键,想较于聚簇索引,由于只有一个字段,所以空间占用非常少。

排序算法

filesort文件排序

文件排序是通过相应的排序算法,把所有的数据拿出来之后在内存中进行排序。使用firlesort排序主要是因为where语句与order by语句使用了不同的索引;order by中的列的索引不同;对索引同时使用ASC和DESC;left join使用右表字段排序等。

TroubleShooting

  • 启动错误,提示server PID file could not be found

    一般是因为MySQL服务卡死了,此时查看进程ps aux | grep mysql*,然后把卡死的给kill掉就行了

  • Access denied for user ‘root’@’localhost’

    出现这种情况,可能是给用户分配了’%’权限,而没有分配localhost权限,我去…

  • WorkBench保持连接不断开: Edit->Preferences->SQL Editor,设置DBMS connection read time out(in seconds)

  • 关于整型数据长度问题,需要注意的是MySQL里面的整型后面跟的长度并不是指该字段的实际长度,而是客户端显示的长度,实际存储的长度可以更长。这是几个整型数据对应的长度表(来自MySQL官网),所以INT无论后面定义的是多少,都是4个字节32位的长度
Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 - 32768 32767
0 65535
MEDIUMINT 3 - 8388608 8388607
0 16777215
INT 4 - 2147483648 2147483647
0 4294967295
BIGINT 8 - 9223372036854775808 9223372036854775807
0 18446744073709551615
  • MySQL分页时出现数据丢失或者数据重复的情况: 如果分页的时候用上了order_by并且目标字段并不是索引字段,那么就有可能出现这种情况,一条数据可能既出现在上一页,又出现在下一页。原因是在mysql5.6以后,priority queue使用的是堆排序,这个排序算法并不稳定,两个相同的值可能在两次排序后的结果不一样。解决方法有两种,一种是给order_by后面的字段加索引,另外一种是增加一个是索引的字段,但是不要把主键放到这里面,否则两个索引都不会使用,导致性能非常低,别问我为什么,我被坑过。参考文章
  • 在查询整型字段的时候空字符串表现得和0一样: 这是MySQL的特性,对于整型字段,空字符串会自动转换成零。另外,对于timestamp字段’’和0000-00-00 00:00:00表现得一样
  • timestamp字段插入的时候出现warnning: data truncated for column,这是因为mysqltimestamp类型不是unix的时间戳,对于非法的字符串插入timestamp的时候结果都是0000-00-00 00:00:00。如果要插入,可以用2017-12-25 12:00:00这种格式,或者使用函数FROM_UNIXTIME(1514177748)进行转换。
  • Invalid use of NULL value: 原因可能是在将列修改为不允许NULL的时候并且已经存在记录该值为null,则不允许修改,这个时候需要先修改已有记录的值。
  • PhpMyAdmin查询正确,但是导出结果时导出的文件里面只有一条错误的sql语句: 尝试把要导出的字段及表名不用别名
haofly wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
坚持原创技术分享,您的支持将鼓励我继续创作!