MySQL 笔记（持续更新）
中文乱码
# 登录
mysql -u root -p
# 查看目前所有编码
mysql> show variables like 'char%';
会显示类似下面的信息：
+--------------------------+-------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+-------------------------------+
注意：
character_set_client -> 客户端字符集
character_set_connection -> 中间转换器字符集
character_set_results -> 查询结果字符集
一般来说，只要这三个字符集和客户端一致，就不会有乱码出现，快速设置的方法为：
-- gbk|utf8
set names utf8;
MyISAM 和 InnoDB
InnoDB 支持事务，MyISAM 不支持事务。
InnoDB 支持数据行锁定，MyISAM 不支持行锁定，只支持锁定整个表。
InnoDB 支持外键，MyISAM 不支持。
InnoDB 不支持全文索引，而 MyISAM 支持。全文索引是指对 char、varchar 和 text 中的每个词（停用词除外）建立倒排序索引，全文索引不支持中文分词，需使用者自行加入空格进行分词。
索引
-- 显示索引
show index from `table_name`;
-- 创建索引
-- ALTER TABLE
alter table `table_name` add index index_name(`column_1`, `column_2`);
alter table `table_name` add unique index_name(`column_name`);
-- CREATE INDEX
create index index_name on `table_name`(`column_name`);
create unique index index_name on `table_name`(`column_name`);
-- 删除索引
drop index index_name on `table_name`;
alter table `table_name` drop index index_name;
-- 重建索引
repair table `table_name` quick;
优化方案：
在
where或者order by涉及的字段上建立索引。表与表的连接字段应加索引。
查询条件有 null 值，索引失效，如：
where status is null。where 包含比较操作符，索引失效，如：
where id <> 3、where id != 3。where 如果使用 or 来连接条件，所有字段都要有索引，否则索引失效。
where 如果使用 in 或者 not in，索引失效。
where 后面条件如果有函数操作，索引失效。
like 如果左边包含 % 号，索引失效，应该使用：
where title like 'search%'。组合索引查询应该符合最左匹配原则。
导出与导入
# 导出
mysql> mysqldump db_name > backup.sql
# 导入
mysql> use db_name;
mysql> source backup.sql;
# Windows 导入
mysql> source D:\backup.sql;
# 非登录导入
mysql -u {user} -p db_name < /path/to/backup.sql
导入备份文件最好用绝对路径，注意在 Windows 下的 sql 文件路径与名称最好是全英文，不要带下划线等，不然可能会报 SQL failed to open file error 22 错误。
查看状态
-- 查看正在运行的线程，除非有 super 权限，否则只能查看当前用户下的线程。
show processlist;
show full processlist;
-- 查看当前打开的表，一般发生在 drop 或者 rename 的操作会有影响。
show open tables;
show open tables from your_db;
-- 查看服务器状态。
show status like '%lock%';
show status like '%Table%';
show status like 'innodb_row_lock%';
-- 查看 InnoDB 引擎信息。
show engine innodb status\G;
-- 查看服务器配置信息。
show variables like '%timeout%';
共享锁与排他锁
共享锁又称读锁，简称S锁，读取数据时创建的锁，当一个事务给一个数据行加锁的时候，其他事务可以读，但不能写，用法：select ... lock in share mode;。
排他锁又称写锁，简称X锁，读取数据时创建的锁，当一个事务加锁的时候，其他事务不能读取，也不能写，但对于一般的 select 语句无影响，用法：select ... for update;。
注意：
对于 insert、update、delete 操作，InnoDB 会自动给数据行加排他锁，对于一般的 select 语句则不会加任何锁。
排他锁需要在语句明确主键的情况下，才会加行级锁，否则会加表级锁，同样的，索引也会影响锁的类型，跟主键差不多。
例如：
select * from goods where id = 1 for update; -- 主键明确，加行锁
select * from goods where id = 99999 for update; -- 无记录，不加锁
select * from goods where id >= 10 for update; -- 主键不明确，加表锁
select * from goods where name = 'goods_name' for update; -- 无主键，加表锁
乐观锁与悲观锁
乐观锁是假设不会发生并发冲突，每次拿数据时认为别人不会去修改，所以不会加锁，只会在更新的时候去判断数据是否有更新，做法可以使用版本号来控制。
悲观锁是假定会发生并发冲突，每次拿数据时认为别人会修改数据，所以拿数据时会加锁，其他拿数据的事务只能等待，例如行级锁，表级锁等。
慢查询日志
通过命令行开启，重启后会失效。
-- 开启
set global slow-query-log=on;
-- 日志文件
set global slow_query_log_file='/var/log/mysql/slow-query.log';
-- 记录没有使用索引的查询
set global log_queries_not_using_indexes=on;
-- 记录 2 秒以上的查询
set global long_query_time=2;
通过配置文件 my.cnf 开启，永久性，需要重启。
[mysqld]
slow-query-log=on
slow_query_log_file="/var/log/mysql/slow-query.log"
log_queries_not_using_indexes=on
long_query_time=2
查看当前慢查询状态：
show variables like '%slow%';
show variables like '%long_query%';
使用 mysqldumpslow 来分析慢日志，使用说明：mysqldumpslow --help。
-- 列出最慢的前 3 个查询
mysqldumpslow -t 3 slow-query.log
-- 列出记录次数最多的前 3 个语句
mysqldumpslow -s c -t 3 slow-query.log
-- 按照查询时间返回前 3 个并含有左连接的语句
mysqldumpslow -s t -t 10 -g "left join" slow-query.log
获取库下的所有表
select table_name from information_schema.tables where table_schema = 'test';
获取库表占用大小
# 每个表的大小
select table_name, (data_length + index_length) / 1024 / 1024 as 'used(mb)', (data_free) / 1024 / 1024 as 'free(mb)' from information_schema.tables where table_schema = 'test';
# 每个数据库的大小
select table_schema, sum(data_length + index_length) / 1024 / 1024 as 'used(mb)', sum(data_free) / 1024 / 1024 as 'free(mb)' from information_schema.tables group by table_schema;
获取删除表语句
# 删除日志表
select group_concat(concat("drop table `", table_schema, "`.`", table_name, "`;") separator " ") as drop_sql from information_schema.tables where table_schema ="test_db" and table_name like "log_%" group by table_schema;
问题记录
2017-06-30
今天在导入 WordPress 数据时报错：Unknown COLLATE: ‘utf8mb4_unicode_520_ci’，看了下，应该是低版本排序规则不支持 utf8mb4_unicode_520_ci 导致的，目标 MySQL 版本为 5.5.55，源备份版本为 5.6.35。
解决方法很简单，把 sql 文件内容里的 utf8mb4_unicode_520_ci 全局替换成 utf8mb4_unicode_ci 就行了。
2019-03-13
今天在 Ubuntu 18.04 下安装 MySQL 5.7 版本，过程中并没有要求输入 root 密码，安装完成之后无法登录，除非加 sudo 命令。
更新：通过命令 cat /var/log/mysqld.log | grep password 查看密码。
# 登录
sudo mysql -u root
# 查看用户信息，可以看到 root 用户使用 auth_socket 来验证密码
mysql> select user, plugin from mysql.user;
+------------------+-----------------------+
| user | plugin |
+------------------+-----------------------+
| root | auth_socket |
| mysql.session | mysql_native_password |
| mysql.sys | mysql_native_password |
+------------------+-----------------------+
# 修改 root 用户密码及插件类型
mysql> update mysql.user set authentication_string=PASSWORD('root'), plugin='mysql_native_password' where user='root';
mysql> flush privileges;
mysql> exit;
# 重启
sudo service mysql restart
# 登录
mysql -u root -p
因为环境是在虚拟机中，为了让宿主机访问，配置远程登录。
# 注释 bind-address = 127.0.0.1
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 登录
mysql -u root -p
# 添加用户 root，允许远程登录
mysql> grant all on *.* to root@'%' identified by 'root' with grant option;
mysql> flush privileges;
mysql> exit;
# 重启
sudo service mysql restart