MySQL常用指令整理

登录MySQL

 mysql (-h ip)(-P 端口) -u 用户名 -p 用户密码

用户操作

创建用户

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';

GRANT ALL PRIVILEGES ON new_database.* TO 'newuser'@'localhost';

更新权限

FLUSH PRIVILEGES;

更新用户

更新用户信息

UPDATE mysql.user SET USER='li4' WHERE USER='wang5';  

更新权限

FLUSH PRIVILEGES;

更新用户密码

UPDATE user SET authentication_string=PASSWORD('new_password') WHERE User='username';

更新权限

FLUSH PRIVILEGES;

更新用户权限

授予用户从指定数据库中查询、插入和更新所有表的权限。

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'user'@'localhost';

授予user所有数据库的所有权限。

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';

撤销用户权限

撤销用户从指定数据库中选择、插入和更新所有表的权限。

REVOKE SELECT, INSERT, UPDATE ON database_name.* FROM 'user'@'localhost';

删除用户所有权限

DROP USER 'username'@'localhost';

更新权限

FLUSH PRIVILEGES;

删除用户

DROP USER 'username'@'localhost';

查看用户权限

查询所有用户及其对应的权限。（Grant_priv列指示用户是否具有授予其他用户的权限，Super_priv列指示用户是否具有超级用户权限）

SELECT User, Host, Grant_priv, Super_priv FROM mysql.user;

数据库操作

创建数据库

创建数据库并指定字符集为 utf8mb4

CREATE DATABASE IF NOT EXISTS `db_name` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE DATABASE  `db_name` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

删除数据库

DROP DATABASE db_name

DROP DATABASE [ IF EXISTS ] db_name

查询数据库

显示所有数据库

show databases;

选择数据库

选择数据库

use db_name;

修改数据库

法一：

如果所有表都是 MyISAM 类型的话，可以直接修改文件夹的名字（把data目录中的db_name目录重命名为new_db_name）。

法二：

利用 mysqldump ，把旧数据库的数据备份，恢复到新库中。

法三：

先更新旧数据库的存储引擎到 MyISAM，然后改了库目录的名字，然后再改回来 INNODB。

法四：

假设源库名是 old_db_name ，目标库名是 new_db_name

首先创建目标库

create database new_db_name;

获取所有源库的表名

use information_schema;
select table_name from TABLES where TABLE_SCHEMA=’old_db_name’;

然后按照以下命令一个个修改

rename table old_db_name.[tablename] to new_db_name.[tablename];

数据表操作

创建数据表

CREATE TABLE

CREATE TABLE … LIKE

CREATE TABLE … SELECT

更新数据表

修改数据表名

法一：

ALTER TABLE old_table_name rename as new_table_name;

法二：

rename table old_table_name to new_table_name;

修改字段

添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名];

注：FIRST 或 AFTER 已存在的字段名用于指定新增字段在表中的位置，如果 SQL 语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。

修改字段数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

删除字段

ALTER TABLE <表名> DROP <字段名>;

修改字段名称

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

更新存储引擎

ALTER TABLE table_name ENGINE=<存储引擎名>;

删除数据表

删除表

drop table if exists table_name;

清空表

truncate table table_name;

删除大表

1. 对表文件（idb文件）做一个硬链接来加速删除
2. 执行drop操作
3. 删除硬链接文件

http://t.zoukankan.com/easydb-p-14185881.html

删除数据后，磁盘空间未释放？

DELETE 只是将数据标识位删除，并没有整理数据文件，当插入新数据后，会再次使用这些被置为删除标识的记录空间，可以使用OPTIMIZE TABLE 来回收未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE table_name;

注：

1. OPTIMIZE TABLE 只对 MyISAM 和 InnoDB表起作用。
2. OPTIMIZE 执行时会将表锁住，所以不要在高峰期使用。也不要经常使用，每月一次就足够了。

查询数据表

显示表

show tables;

表的详细描述

describe tablename;

导出数据字典

导出数据字典

SELECT
	TABLE_SCHEMA AS '数据库名',
	TABLE_NAME AS '表名',
	COLUMN_NAME AS '列名',
	COLUMN_TYPE AS '类型',
	COLUMN_DEFAULT AS '默认值',
	IS_NULLABLE AS '允许为空',
	DATA_TYPE AS '数据类型',
	CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
	NUMERIC_PRECISION AS '数字精度',
	NUMERIC_SCALE AS '小数位数',
	COLUMN_COMMENT AS '字段说明' 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = 'db_name'

数据操作

插入数据

INSERT VALUES

INSERT INTO 表名 （列明,列明1,列明2...）VALUES(数值,数值1,数值2,数值3)

INSERT SET

INSERT INTO 表名 SET 列名=值,列名1 = 值1,列名2=值2...

删除数据

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

删除所有数据

DELETE FROM <表名>;

truncate table <表名>;

删除海量数据

TRUNCATE

使用 TRUNCATE 语句清空表

TRUNCATE TABLE table_name;

使用分区表

创建分区表

PARTITION BY RANGE (column_name) (
    PARTITION p0 VALUES LESS THAN (value1),
    PARTITION p1 VALUES LESS THAN (value2),
    ...
);

使用 ALTER TABLE 语句删除特定分区中的数据

此操作不可逆。

ALTER TABLE table_name TRUNCATE PARTITION partition_name;

partition_name ：要删除数据的分区名。

先查询ID再删除

1. 先根据条件查询的id值。
2. 然后用ID作为查询条件进行 DELETE 操作。
3. 循环上述步骤直至数据删除完毕。

更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

查询数据

单条件查询的运算符

• =
• !=，<>
• >
• <
• >=
• <=

多条件查询连接符

• AND
• OR

多条件查询运算符

• IN
• BETWEEN ... AND ...
• NOT

排序

• ORDER BY
  • ASC：升序排序（从小到大）（默认）
  • DESC：降序排序（从大到小）

去重

• DISTINCT

模糊查询

以匹配值开头

SELECT *  FROM <表名> WHERE <字段名> like '%<匹配值>';

以匹配值结尾

SELECT *  FROM <表名> WHERE <字段名> like '<匹配值>%';

包含匹配值

SELECT *  FROM <表名> WHERE <字段名> like '%<匹配值>%';

分页查询

SELECT * FROM <表名> LIMIT Start,PageSize

• LIMIT：分页查询关键字
• Start：开始行索引，默认是0
• PageSize：查询条数

聚合查询

• count(col): 表示求指定列的总行数
• max(col): 表示求指定列的最大值
• min(col): 表示求指定列的最小值
• sum(col): 表示求指定列的和
• avg(col): 表示求指定列的平均值

COUNT

统计所有的列，相当于行数，统计结果中会包含字段值为null的列；

SELECT count(*) FROM <表名>;

只包含列名的列，统计表中出现该字段的次数，并且不统计字段为null的情况；

SELECT count(<字段名>) FROM <表名>;

可以统计表中所有数据，不统计所有的列，用1代表代码行，在统计结果中包含列字段为null的数据；

SELECT count(1) FROM <表名>;

注：

1. 列名为主键，count(列名) 比 count(1) 快；
2. 列名不为主键，count(1) 会比 count(列名)快；
3. 如果表中多个列并且没有主键，则 count(1) 的执行效率优于 count(*)；
4. 如果有主键，则 count(主键) 的执行效率是最优的；
5. 如果表中只有一个字段，则 count(*) 最优。

SUM

SELECT sum(<字段名>) FROM <表名>;

MAX

SELECT max(<字段名>) FROM <表名>;

MIN

SELECT min(<字段名>) FROM <表名>;

AVG

SELECT avg(<字段名>) FROM <表名>;

去重查询

SELECT distinct(<字段名>) FROM <表名>;

分组查询

SELECT * FROM <表名> group by <字段名1>,<字段名2>,……;

并集查询

UNION

对两个结果集进行并集操作，不包括重复行，相当于 distinct ， 同时进行默认规则的排序

SELECT <字段名> FROM <表名1>
UNION
SELECT <字段名> FROM <表名1>;

UNION ALL

对两个结果集进行并集操作，包括重复行，即所有的结果全部显示，不管是不是重复

SELECT <字段名> FROM <表名1>
UNION ALL
SELECT <字段名> FROM <表名1>;

注：

• union all 只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用 union all 的执行效率要比 union高

全局操作

最大并发数

MySQL 实例的最大连接数，上限值是16384，默认值151

show variables like "max_connections";

每个数据库用户的最大连接数，即并发值，默认0

show variables like "max_user_connections";

优化

一般要求两者比值超过10%

max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%

临时修改

set global max_connections = 200;

注：永久修改需修改配置文件

连接超时时间

show global variables like '%timeout';

wait_timeout：MySQL在关闭一个非交互的连接之前所要等待的秒数，参数默认值：28800秒（8小时）

interactive_timeout：MySQL在关闭一个交互的连接之前所要等待的秒数，参数默认值：28800秒（8小时）

临时修改

set global wait_timeout=300,interactive_timeout=300;

注：

1. 永久修改需修改配置文件。
2. 同时设置 interactive_timeout ，wait_timeout的设置才会生效

# 最大睡眠时间
wait_timeout=300
# 超时时间设置
interactive_timeout = 500

1055报错

Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在配置文件中添加此项，然后重启mysql服务：

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

最大错误连接数

max_connect_errors 是一个 MySQL 中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。

不推荐提高 max_connection_errors 值：

在 MySQL 中重置最大错误连接数（默认的 max_connection_errors =10，可适当调大）

flush hosts;

在配置文件中添加此项，然后重启mysql服务：

[mysqld]
max_connect_errors = 1000

在数据库中添加 max_connection_errors

show variables like '%max_connection_errors%';
set global max_connect_errors = 1000;

数据库大小

查询整个数据库的大小（以 MB 为单位）

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from information_schema.TABLES

SELECT
	table_schema AS '数据库',
	sum( table_rows ) AS '记录数',
	sum( TRUNCATE ( data_length / 1024 / 1024, 2 ) ) AS '数据容量(MB)',
	sum( TRUNCATE ( index_length / 1024 / 1024, 2 ) ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES;

查询某个库的大小

SELECT
	concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS DATA 
FROM
	information_schema.TABLES 
WHERE
	table_schema = <库名>

SELECT
	table_schema AS '数据库',
	sum( table_rows ) AS '记录数',
	sum( TRUNCATE ( data_length / 1024 / 1024, 2 ) ) AS '数据容量(MB)',
	sum( TRUNCATE ( index_length / 1024 / 1024, 2 ) ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
WHERE
	table_schema = <库名>;

查询某个表的大小

SELECT
	concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'MB' ) AS DATA 
FROM
	information_schema.TABLES 
WHERE
	table_schema = <库名>
	AND table_name = <表名>;

SELECT
	table_schema AS '数据库',
	table_name AS '表名',
	table_rows AS '记录数',
	sum( TRUNCATE ( data_length / 1024 / 1024, 2 ) ) AS '数据容量(MB)',
	sum( TRUNCATE ( index_length / 1024 / 1024, 2 ) ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
WHERE
	table_schema = <库名>
	AND table_name = <表名>;

查看各数据库容量大小

SELECT
	table_schema AS '数据库',
	sum( table_rows ) AS '记录数',
	sum(
	TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
	sum(
	TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
GROUP BY
	table_schema 
ORDER BY
	sum( data_length ) DESC,
	sum( index_length ) DESC;

查看指定数据库各表容量大小

SELECT
	table_schema AS '数据库',
	table_name AS '表名',
	table_rows AS '记录数',
	TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
	TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
WHERE
	table_schema = <库名>
ORDER BY
	data_length DESC,
	index_length DESC;

事务锁

1205 - Lock wait timeout exceeded; try restarting transaction

出现此错误是因为语句被锁住了，所以释放这个锁，
首先查询被锁的语句

select * from information_schema.innodb_trx;

执行kill命令

kill 141865400;

锁表

• 锁表发生在insert update 、delete 中；
• 数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生 commite 或者 回滚 或者退出数据库用户；

查看锁表进程

show processlist;

或

SELECT * FROM information_schema.PROCESSLIST;

查询正在执行的进程

SELECT * FROM information_schema.PROCESSLIST where length(info) >0;

查询是否锁表

show OPEN TABLES where In_use > 0;

查看被锁住的

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

8.0.13版本之后

SELECT * FROM PERFORMANCE_SCHEMA.data_locks;

等待锁定

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

8.0.13版本之后

SELECT * FROM PERFORMANCE_SCHEMA.data_lock_waits;

杀掉锁表进程

kill <id>

批量杀进程

SELECT concat( 'KILL ', id, ';' ) FROM information_schema.PROCESSLIST 
WHERE USER = 'root';

将查询结果导出到文件

SELECT
	concat( 'KILL ', id, ';' ) 
FROM
	information_schema.PROCESSLIST 
WHERE
	USER = 'root' 
INTO OUTFILE '/tmp/a.txt';

执行文件

source /tmp/a.txt;

数据迁移

mysqldump

导出

导出所有数据库

mysqldump -h [host] --port [port] -u[user] -p [db_name] --default-character-set=utf8mb4 --all-databases > [file_path]

导出整个数据库

mysqldump -h [host] --port [port] -u[user] -p [db_name] > [file_path]

导出一个表

mysqldump -h [host] --port [port] -u[user] -p [db_name] [table_name] > [file_path]

导出一个表的部分数据

mysqldump -h [host] --port [port] -u[user] -p [db_name] [table_name] --where=[sql] > [file_path]

导出一个表的结构

mysqldump -h [host] --port [port] -u[user] -p -d --add-drop-table [db_name] [table_name] > [file_path]

导入

source命令

source [file_path]

导入数据到数据库

mysql -uroot -p [db_name] < [file_path]

导入数据到数据表

mysql -uroot -p [db_name][table_name] < [file_path]

CREATE SELECT

只复制表结构到新表

CREATE TABLE <new_table _name> LIKE <old_table_name>;

复制表结构及数据到新表

CREATE TABLE new_table_name SELECT [field1,field2... | *] FROM old_table_name;

SELETE INTO OUTFILE

导出

SELECT * FROM <表名>
INTO OUTFILE '绝对路径/文件名.txt'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

导入

LOAD DATA INFILE '绝对路径/文件名.txt' 
INTO TABLE <表名> 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

注：

• FIELDS TERMINATED BY ',' ：指定字段间的分隔符，默认’\t’。
• OPTIONALLY ENCLOSED BY '"' ：无视这些个双引号
• LINES TERMINATED BY '\n'：指定每行结尾的字符，默认’\n’。
• IGNORE 1 LINES ：忽视文件的前几行

其他

最大数据量

MySQL 没有限制单表最大记录数，最大数据量取决于操作系统对文件大小的限制。

文件系统	数据量
FAT16（Windows）	支持最大分区2GB，最大文件2GB；
FAT32（Windows）	支持最大分区128GB，最大文件4GB；
NTFS（Windows）	支持最大分区2TB，最大文件2TB；
ExtFAT
ext2	支持最大分区4TB，最大文件2GB；
ext3	支持最大分区4TB，最大文件2GB；
ext4
minix
HPFS（OS/2）	支持最大分区2TB，最大文件2GB；
VFAT
JFS（AIX）
XFS（IRIX）

注：表中数据待验证

性能指标查询

查询吞吐量（TPS）

客户端每发送一个查询语句，其值就会加一，可利用其来衡量查询吞吐量

SHOW GLOBAL STATUS LIKE "Questions";

查询 查询操作的次数

show status like 'com_select';

Writes

show status like 'Com_insert';
show status like 'Com_update';
show status like 'Com_delete';

Writes = Com_insert + Com_update + Com_delete

查询性能

1)查询性能模式

按模式计算以微秒为单位的平均运行时间

SELECT schema_name 
 , SUM(count_star) count  
 , ROUND( (SUM(sum_timer_wait) / SUM(count_star))  
 / 1000000) AS avg_microsec FROM performance_schema.events_statements_summary_by_digest  
 WHERE schema_name IS NOT NULL  
 GROUP BY schema_name; 

按模式计算出现错误的语句总数

SELECT schema_name
     , SUM(sum_errors) err_count
  FROM performance_schema.events_statements_summary_by_digest 
  WHERE schema_name IS NOT NULL 
  GROUP BY schema_name;

2）sys模式

查询最慢的语句

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

查看哪些标准化语句出现了错误：

SELECT * FROM sys.statements_with_errors_or_warnings; 

3)慢查询

连接情况

查询连接MySQL服务器的次数

show status like 'connections';

监控连接使用率

show status like 'Threads%';

• Threads_cached： 代表当前此时此刻线程缓存中有多少空闲线程。
• Threads_connected：记录连接的线程数，每个连接对应一个线程
• Threads_created： 代表从最近一次服务启动，已创建线程的数量。
• Threads_running：代表当前激活的（非睡眠状态）线程数。帮助你分隔在任意时间正在积极处理查询的线程与那些虽然可用但是闲置的连接。

show variables like '%max_connection_errors%';

• Connection_errors_max_connections：连接数超过max时，该指标会开始增加，同时追踪所有失败连接尝试的 Aborted_connects 指标也会开始增加

SHOW GLOBAL STATUS LIKE "%Connection_errors_internal";

• Connection_errors_internal：该指标只会在错误源自服务器本身时增加。内部错误可能反映了内存不足状况，或者服务器无法开启新的线程。

连接IP

查看所有连接的客户端 ip

SELECT substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state,host_name;

会列出每个 ip 当前的状态，以及当前的连接数 。这个在处理类似碰到数据库 Too Many Connections 等的错误的时候比较有用。

客户端 IP 分组，看哪个客户端的链接数最多

select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

缓冲池使用情况

缓冲池大小

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";

缓冲池块数

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

已使用的缓存 “页Page”的数量

show global status like "innodb_buffer_pool_pages_data"

全部缓存页的数量

show global status like "innodb_buffer_pool_pages_total";

缓存页的每一页的长度

show global status like "Innodb_page_size";

页面使用率

result = innodb_buffer_pool_pages_data / innodb_buffer_pool_pages_total * 100%

注：可以利用这个结果作为mysql服务器优化的参考指标

• result > 95%，考虑增大 innodb_buffer_pool_size ，建议使用物理内存的75%
• result < 95%，考虑减小 innodb_buffer_pool_size ，建议设置为：innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 （MB）

其他

显示当前MySQL版本和当前日期

select version(),current_date;

查看数据库的存储路径

SHOW VARIABLES LIKE '%secure%';

查看数据库支持的引擎和默认数据库引擎代码

show engines;

查询MySQL服务器上线的时间

show status like 'uptime';

查询慢查询的次数

show status like 'slow_queries';

查看MySQL服务器运行的各种状态值 ：

show global status;

查询是否有锁

show OPEN TABLES where In_use > 0;

参考文档

https://m.php.cn/article/461258.html

https://blog.csdn.net/weixin_30276623/article/details/113910357

http://t.zoukankan.com/easydb-p-14185881.html

https://blog.csdn.net/qq_49912622/article/details/122882008

https://blog.csdn.net/horses/article/details/119720521

https://blog.csdn.net/weixin_67033761/article/details/125316883

https://www.yisu.com/zixun/620726.html

https://blog.csdn.net/qq_44866828/article/details/124613215

http://t.zoukankan.com/qczy-p-14902152.html

https://www.toutiao.com/article/6793687879626261003

https://www.toutiao.com/article/7100588132675961355

https://www.toutiao.com/article/6609797717734457859

https://www.cnblogs.com/liuchunxiao83/p/16436678.html

https://www.panziye.com/java/4518.html

https://www.csdn.net/tags/NtDaYg4sMjY4NjEtYmxvZwO0O0OO0O0O.html

http://www.wjhsh.net/young233-p-13441930.html

https://blog.51cto.com/u_14286115/5004806

https://blog.csdn.net/u011957758/article/details/77116037

https://blog.frognew.com/2015/12/mysql-status-and-variables.html

https://blog.csdn.net/zhangcongyi420/article/details/113030932

https://m.php.cn/article/460257.html

https://blog.csdn.net/chuixue24/article/details/120433765

https://www.yisu.com/zixun/495066.html

https://blog.csdn.net/qq_40276626/article/details/119236666

https://www.cnblogs.com/zengpeng/p/15292569.html

https://baijiahao.baidu.com/s?id=1643716664333296701

https://www.jb51.net/article/58093.htm

https://blog.csdn.net/qq_33246702/article/details/106797802

https://wenku.baidu.com/view/d61d483ffc00bed5b9f3f90f76c66137ee064fba.html

https://blog.csdn.net/xintingandzhouyang/article/details/105003015

https://www.cnblogs.com/qtt1994/p/13214035.html

https://zhuanlan.zhihu.com/p/640034575