MySQL常用命令总结

前言

MySQL是一个功能强大且广泛使用的开源关系型数据库管理系统。随着互联网和数据应用的不断发展，熟练掌握MySQL的使用对于开发人员和数据库管理员来说变得越来越重要。MySQL提供了丰富的命令和功能，能够满足各种数据库操作需求。本文将总结一些常用的MySQL命令，帮助大家更好地掌握和使用MySQL。

1、查看表状态

show table status like '%tablename%';

2、改密码

-- 方法一：

./mysqladmin -uroot -hlocalhost --socket=/data/mysql_3306/tmp/mysql.sock -p password
-- 方法二：

ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY 'password';

3、杀特定用户链接

select concat('KILL ',id,';')from information_schema.processlist where user='21xmt_user';

4、binlog解析

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201

5、删除用户

drop user 'xxx'; 只删除 'xxx'@'%' 账户

6、修改主键（小表，大表用pt-osc等工具）

alter table xxx drop primary key,add primary key(task_id, aaa);

7、查看表大小

select table_name , table_rows from inforation_schema.tables where table_name='xxx';

8、权限查询

-- 1. 查询用户的权限：

SHOW GRANTS FOR '用户名'@'主机名';
-- 其中，'用户名'是你想查询权限的用户，'主机名'是连接该用户的主机名。

-- 2. 查询所有用户的权限：

SELECT user, host, authentication_string, plugin, password_expired FROM mysql.user;
-- 该查询语句将返回所有用户的用户名、主机名、认证字符串、插件和密码过期信息等。

-- 3. 查询用户拥有的权限：

SELECT * FROM mysql.user WHERE User = '用户名' AND Host = '主机名';
-- 更改上述查询语句中的'用户名'和'主机名'为你想查询权限的用户和主机名。

4. 查询用户在特定数据库上的权限：

SHOW GRANTS FOR '用户名'@'主机名' ON '数据库名';
-- 将'用户名'替换为要查询权限的用户，'主机名'设置为连接该用户的主机名，'数据库名'为目标数据库。

注意，执行上述查询需要具有足够的权限，一般只有具有SUPER权限或拥有grant权限的用户才能查询其他用户的权限信息。

9、trace

SET SESSION OPTIMIZER_TRACE="enabled=on";   # enable tracing
<statement to trace>;   # like SELECT, EXPLAIN SELECT, UPDATE, DELETE...
SELECT * FROM information_schema.OPTIMIZER_TRACE;
[ repeat last two steps at will ]
SET SESSION OPTIMIZER_TRACE="enabled=off"; # disable tracing

• SET SESSION OPTIMIZER_TRACE="enabled=on";此指令用于启用查询优化器跟踪。将其设置为"enabled=on"表示启用跟踪功能。此配置仅对当前会话有效。

• <statement to trace>;在该指令中，你可以执行要进行跟踪的SQL语句，例如SELECT、EXPLAIN SELECT、UPDATE、DELETE等。查询优化器将在执行该语句的同时记录相关信息以进行跟踪。

• SELECT * FROM information_schema.OPTIMIZER_TRACE;这个查询语句用于检索跟踪结果。执行后，将返回包含查询优化器跟踪信息的结果集。你可以通过分析这些信息来了解优化器是如何处理和优化查询的。

• [ repeat last two steps at will ]这两个步骤可以重复多次，以跟踪和分析不同的查询语句。

• SET SESSION OPTIMIZER_TRACE="enabled=off";此指令用于禁用查询优化器跟踪。将其设置为"enabled=off"表示禁用跟踪功能。同样，此配置仅对当前会话有效。

10、innodb状态查看

SHOW ENGINE INNODB STATUS;

执行上述语句后，会返回一个结果集，其中包含了InnoDB引擎的详细状态信息。可以查看到InnoDB的版本、事务和锁定的信息、缓冲池的统计信息等。

11、修改root密码

方式一：使用ALTER USER语句

-- 将 'new_password' 替换为你要设置的新密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

-- 执行完成后刷新权限，以使修改后的密码生效
flush privileges;

方式二：使用SET PASSWORD语句

-- 将 'new_password' 替换为你要设置的新密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

--执行完成后刷新权限，以使修改后的密码生效
FLUSH PRIVILEGES;

12、添加自增属性

alter table xxx modify id bigint auto_increment;

13、rename库名 (所有表操作即可完成库名重命名)

RENAME DATABASE old_database_name TO new_database_name;

将上述语句中的"old_database_name"替换为要重命名的现有数据库的名称，将"new_database_name"替换为所需的新数据库名称。

请注意以下事项：

• 仅适用于空数据库：RENAME DATABASE语句只能重命名空数据库。如果数据库中有表或其他对象，则无法使用该语句。
• 权限要求：执行RENAME DATABASE需要具有足够的权限（如CREATE、DROP和ALTER权限）。
• 慎重操作：重命名数据库是一个潜在的高风险操作，应该在确认备份和可能的影响后谨慎执行。

14、表碎片整理

OPTIMIZE TABLE table_name;
-- 将上述语句中的"table_name"替换为要进行碎片整理的表名。可以一次指定多个表，用逗号分隔。

碎片整理会重新组织表的物理存储结构，删除不再使用的空间和优化表的性能。它可以回收被删除数据所占用的空间并重组数据行，提高查询性能。

请注意以下事项：

• 权限要求：执行OPTIMIZE TABLE需要具有足够的权限（如ALTER和CREATE权限）。
• 表锁定：执行OPTIMIZE TABLE时，表将被锁定，可能会影响其他对表的操作。在生产环境中，应该在合适的时间段执行该操作，避免对用户的影响。
• 自动碎片整理：MySQL InnoDB存储引擎默认支持自动碎片整理，并且在某些情况下会自动执行碎片整理。
  –碎片整理操作并不是在每个MySQL版本或存储引擎中都是可用的。在执行碎片整理之前，建议先进行数据备份，并测试对性能的影响。

15、查看表的最近更新时间

-- 使用INFORMATION_SCHEMA表
SELECT UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

16、创建索引时间查询

SELECT trx_id, trx_started, (NOW() - trx_started) trx_duration_seconds, id processlist_id, user, IF(LEFT(HOST, (LOCATE(':', host) - 1)) = '', host,  LEFT(HOST, (LOCATE(':', host) - 1))) host, command, time, REPLACE(SUBSTRING(info,1,25),'\n','') info_25 FROM information_schema.innodb_trx JOIN information_schema.processlist  ON innodb_trx.trx_mysql_thread_id = processlist.id WHERE (NOW() - trx_started) > 60 ORDER BY trx_started;

17、mysql 终端中操作启用事务

begin；  -- 用于启动一个新的事务
要执行的sql；
commit/rollback；

-- commit用于提交事务，将之前的修改永久保存到数据库中
-- rollback命令用于回滚事务，撤销之前的修改，将数据库恢复到事务开始前的状态

启用事务后，在提交或回滚前，对数据库做的任何修改都只是在事务的上下文中进行的，对外部用户是不可见的。只有在显式执行 COMMIT; 或 ROLLBACK; 命令后，其他用户才能看到你的修改结果。

18、修改表的字符集

使用 ALTER TABLE 语句加上 CONVERT TO 子句，指定新的字符集来修改表的字符集。例如，如果要将表的字符集改为UTF-8，可以这样操作：

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8;

如果你想同时修改表的字符集和校对规则（collation），可以使用下面的命令：

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

这里的 utf8_general_ci 是UTF-8字符集的一个常见的校对规则。
修改表字符集后，你可以使用 SHOW CREATE TABLE 命令再次查看表的详细信息，确认字符集的修改是否生效。

19、查看用户自定义视图

存储过程、triggers查询方法类似，自己找相关表查询即可。

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

总结

本文对MySQL常用命令进行了总结，涵盖了数据库的创建、连接、操作、查询、优化和备份等方面。通过学习和掌握这些命令，就可以完成基本的数据库操作，提高数据库性能，保证数据安全性。

学习MySQL是一个长期的过程，不断实践和探索才能更好地掌握。祝愿大家在使用MySQL时能够取得更好的效果，为自己的项目和工作创造更大的价值。