MySQL常用命令总结

2024-03-24 1936阅读

前言
- 1、查看表状态
- 2、改密码
- 3、杀特定用户链接
- 4、binlog解析
- 5、删除用户
- 6、修改主键（小表，大表用pt-osc等工具）
- 7、查看表大小
- 8、权限查询
- 9、trace
- 10、innodb状态查看
- 11、修改root密码
- 12、添加自增属性
- 13、rename库名 (所有表操作即可完成库名重命名)
- 14、表碎片整理
- 15、查看表的最近更新时间
- 16、创建索引时间查询
- 17、mysql 终端中操作启用事务
- 18、修改表的字符集
- 19、查看用户自定义视图
- 总结
  前言
  
  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
;   # 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"表示启用跟踪功能。此配置仅对当前会话有效。
  - ;在该指令中，你可以执行要进行跟踪的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时能够取得更好的效果，为自己的项目和工作创造更大的价值。