MENU

Catalog

MySQL 数据库常用命令 All in One

March 14, 2026 • MySQL

本文整理了 MySQL 中数据库、用户、数据表相关的常用命令，适合作为开发、测试、运维日常使用的速查手册。

全文分为三部分：

  1. 数据库部分：创建、删除数据库，修改数据库字符集
  2. 数据用户部分：创建用户、远程用户、授权、授予 PROCESS 权限、创建与 root 类似权限的 admin 用户
  3. 数据表部分：创建表、修改表字段、修改表名、修改字段类型、新增字段、删除表

说明：

  • 适用于 MySQL 5.7 / 8.0 常见场景
  • 涉及用户、授权的操作，通常需要 root 或具备相应权限的账号执行
  • 示例数据库名统一使用：mydb
  • 示例用户名统一使用：devuser、admin

一、数据库部分

1. 查看所有数据库

SHOW DATABASES;

2. 创建数据库

2.1 基本创建

CREATE DATABASE mydb;

2.2 创建数据库并指定字符集和排序规则

推荐显式指定字符集，避免后续乱码问题。

CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;

2.3 如果数据库不存在则创建

CREATE DATABASE IF NOT EXISTS mydb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;

3. 删除数据库

DROP DATABASE mydb;

如果存在则删除：

DROP DATABASE IF EXISTS mydb;
注意：删除数据库会同时删除该数据库下的所有表和数据，且不可恢复。

4. 查看数据库创建语句

SHOW CREATE DATABASE mydb;

5. 切换数据库

USE mydb;

6. 修改数据库字符集

ALTER DATABASE mydb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;

说明：

  • 该命令只会修改数据库的默认字符集
  • 不会自动修改已有表和字段的字符集

如果还要修改已有表的字符集，可以执行：

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

例如：

ALTER TABLE user_info CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

二、数据用户部分

1. 查看当前用户

SELECT USER();

查看当前真正生效的账号：

SELECT CURRENT_USER();

2. 查看所有用户

SELECT user, host FROM mysql.user;

如果还想同时查看用户使用的认证插件：

SELECT user, host, plugin FROM mysql.user;

3. 创建用户

3.1 创建本地用户

CREATE USER 'devuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

3.2 创建允许任意主机连接的用户

CREATE USER 'devuser'@'%' IDENTIFIED BY 'StrongPassword123!';
% 表示允许任意主机连接，适合远程访问，但存在安全风险，生产环境建议限制为具体 IP。

3.3 创建指定 IP 可连接的远程用户

CREATE USER 'devuser'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';

3.4 如果用户已存在则先删除再创建

DROP USER IF EXISTS 'devuser'@'%';
CREATE USER 'devuser'@'%' IDENTIFIED BY 'StrongPassword123!';

4. 创建用户时可选的多种加密方式

MySQL 不同版本支持的认证插件不同。
创建用户时，可以使用 IDENTIFIED WITH ... BY ... 指定认证方式。

4.1 查看当前默认认证插件

SHOW VARIABLES LIKE 'default_authentication_plugin';

4.2 查看当前用户使用的认证插件

SELECT user, host, plugin FROM mysql.user;

4.3 默认方式创建用户

CREATE USER 'devuser'@'%' IDENTIFIED BY 'StrongPassword123!';

说明：

  • 不显式指定认证插件时，MySQL 会使用服务器当前默认认证方式
  • 在 MySQL 8.0 中，常见默认认证方式是 caching_sha2_password
  • 在旧版本或兼容场景中，常见的是 mysql_native_password

4.4 使用 caching_sha2_password 创建用户

这是 MySQL 8.0 推荐的认证方式，安全性更高。

CREATE USER 'devuser'@'%'
IDENTIFIED WITH caching_sha2_password BY 'StrongPassword123!';

特点：

  • 安全性较高
  • 是 MySQL 8.0 常见默认方式
  • 某些旧客户端可能不兼容

适合场景：

  • MySQL 8.0 新项目
  • 使用较新驱动和客户端工具的环境

4.5 使用 mysql_native_password 创建用户

这是传统认证方式，兼容性更好。

CREATE USER 'devuser'@'%'
IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

特点：

  • 兼容性较好
  • 旧系统、旧客户端中非常常见
  • 安全性通常不如 caching_sha2_password

适合场景：

  • 老系统迁移
  • 老版本 JDBC、Navicat、客户端驱动兼容问题

4.6 使用 sha256_password 创建用户

CREATE USER 'devuser'@'%'
IDENTIFIED WITH sha256_password BY 'StrongPassword123!';

特点：

  • 安全性较高
  • 使用相对较少
  • 某些环境下配置与兼容性不如前两种常见

4.7 使用 auth_socket 创建用户

auth_socket 主要用于 Linux 本机通过操作系统用户进行认证，不依赖传统密码。

CREATE USER 'devuser'@'localhost'
IDENTIFIED WITH auth_socket;

特点：

  • 不需要密码
  • 一般只能本机登录
  • 常见于服务器本机管理场景
注意：使用 auth_socket 后，通常不能像普通密码用户一样远程连接。

4.8 修改已有用户的认证插件

如果用户已经存在，也可以通过 ALTER USER 调整认证方式。

改为 caching_sha2_password：

ALTER USER 'devuser'@'%'
IDENTIFIED WITH caching_sha2_password BY 'StrongPassword123!';

改为 mysql_native_password：

ALTER USER 'devuser'@'%'
IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

改为 sha256_password：

ALTER USER 'devuser'@'%'
IDENTIFIED WITH sha256_password BY 'StrongPassword123!';

4.9 直接使用密码哈希值创建用户

某些场景下，可以不用明文密码，而是直接指定加密后的哈希值。

CREATE USER 'devuser'@'%'
IDENTIFIED WITH mysql_native_password AS '*HASH_VALUE_HERE';

或者修改已有用户：

ALTER USER 'devuser'@'%'
IDENTIFIED WITH mysql_native_password AS '*HASH_VALUE_HERE';

说明：

  • BY '明文密码'：由 MySQL 自动加密
  • AS '哈希值'：直接使用已有的密码哈希
注意：不同认证插件的哈希格式不同，不能混用。

4.10 创建用户时的推荐建议

MySQL 8.0 新项目推荐

CREATE USER 'devuser'@'%'
IDENTIFIED WITH caching_sha2_password BY 'StrongPassword123!';

需要兼容旧客户端时推荐

CREATE USER 'devuser'@'%'
IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

本机免密码管理账号示例

CREATE USER 'admin_local'@'localhost'
IDENTIFIED WITH auth_socket;

5. 删除用户

DROP USER 'devuser'@'localhost';

如果存在则删除：

DROP USER IF EXISTS 'devuser'@'localhost';

6. 修改用户密码

6.1 修改当前用户密码

ALTER USER USER() IDENTIFIED BY 'NewPassword123!';

6.2 修改指定用户密码

ALTER USER 'devuser'@'%' IDENTIFIED BY 'NewPassword123!';

7. 授予数据库权限

7.1 授予某个数据库的常用权限

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
ON mydb.* TO 'devuser'@'%';

说明：

  • mydb.* 表示 mydb 数据库下所有表
  • 适合普通开发、测试账号使用

7.2 授予某数据库全部权限

GRANT ALL PRIVILEGES ON mydb.* TO 'devuser'@'%';
ALL PRIVILEGES 表示当前层级上的全部权限。这里授予的是 mydb 数据库层级的全部权限，不是全局权限。

7.3 授予“可为其他人授权”的权限

如果希望用户不仅能使用这些权限，还能把这些权限再授权给别人，需要加上 WITH GRANT OPTION。

GRANT ALL PRIVILEGES ON mydb.* TO 'devuser'@'%'
WITH GRANT OPTION;

含义：

  • 用户拥有 mydb 的全部权限
  • 用户可以把自己拥有的权限授予其他用户

7.4 授予“不可为其他人授权”的权限

不加 WITH GRANT OPTION 即可：

GRANT ALL PRIVILEGES ON mydb.* TO 'devuser'@'%';

含义：

  • 用户可以使用这些权限
  • 但不能继续授权给别人

8. 授予 PROCESS 权限

PROCESS 是全局权限，常用于查看当前线程、连接、正在执行的 SQL。

GRANT PROCESS ON *.* TO 'devuser'@'%';

说明：

  • PROCESS 必须授予在 *.* 上
  • 常用于管理监控、排查慢 SQL、查看会话状态等场景

9. 创建与 root 类似权限的 admin 用户

如果需要创建一个高权限管理员账号，可以这样做：

CREATE USER 'admin'@'%' IDENTIFIED BY 'AdminPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

如果希望限制为本机登录：

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'AdminPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

说明：

  • admin 将拥有几乎与 root 类似的数据库管理权限
  • WITH GRANT OPTION 表示其还可以给其他用户授权
注意：生产环境请谨慎使用 ALL PRIVILEGES ON *.* 和 WITH GRANT OPTION。

如果你还希望显式指定认证方式，例如使用兼容性更好的 mysql_native_password：

CREATE USER 'admin'@'%'
IDENTIFIED WITH mysql_native_password BY 'AdminPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

10. 查看用户权限

10.1 查看当前用户权限

SHOW GRANTS;

10.2 查看指定用户权限

SHOW GRANTS FOR 'devuser'@'%';

11. 撤销权限

11.1 撤销某数据库上的部分权限

REVOKE INSERT, UPDATE, DELETE ON mydb.* FROM 'devuser'@'%';

11.2 撤销某数据库上的全部权限

REVOKE ALL PRIVILEGES ON mydb.* FROM 'devuser'@'%';

11.3 撤销授权他人的能力

REVOKE GRANT OPTION ON mydb.* FROM 'devuser'@'%';

11.4 撤销 PROCESS 权限

REVOKE PROCESS ON *.* FROM 'devuser'@'%';

12. 刷新权限

在 MySQL 8.0 中，CREATE USER、ALTER USER、GRANT、REVOKE 等命令通常会立即生效，一般不需要执行 FLUSH PRIVILEGES。

但如果你手动修改了 mysql.user 等系统表，则需要：

FLUSH PRIVILEGES;

三、数据表部分

1. 查看当前数据库所有表

SHOW TABLES;

2. 查看表结构

DESC 表名;

或者：

DESCRIBE 表名;

查看建表语句：

SHOW CREATE TABLE 表名;

3. 创建表

下面给出一个完整示例：

CREATE TABLE user_info (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(100) NOT NULL COMMENT '密码',
    email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
    age INT DEFAULT 0 COMMENT '年龄',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

如果表不存在则创建

CREATE TABLE IF NOT EXISTS user_info (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. 修改表名

4.1 使用 RENAME TABLE

RENAME TABLE user_info TO user_account;

4.2 使用 ALTER TABLE

ALTER TABLE user_info RENAME TO user_account;

5. 修改表字段

5.1 修改字段名

ALTER TABLE user_info
CHANGE old_column new_column VARCHAR(100);

例如把 username 改为 login_name：

ALTER TABLE user_info
CHANGE username login_name VARCHAR(50) NOT NULL COMMENT '登录名';
注意：CHANGE 即使只修改字段名，也要把完整字段定义写出来。

5.2 修改字段类型

ALTER TABLE user_info
MODIFY age BIGINT DEFAULT 0;

例如把 email 长度从 VARCHAR(100) 改为 VARCHAR(200)：

ALTER TABLE user_info
MODIFY email VARCHAR(200) DEFAULT NULL COMMENT '邮箱';
MODIFY 常用于修改字段类型、长度、默认值、注释等，不修改字段名。

5.3 同时修改字段名和类型

ALTER TABLE user_info
CHANGE age user_age INT DEFAULT 0 COMMENT '用户年龄';

6. 新增表字段

6.1 在最后新增字段

ALTER TABLE user_info
ADD phone VARCHAR(20) DEFAULT NULL COMMENT '手机号';

6.2 在指定字段后新增字段

ALTER TABLE user_info
ADD phone VARCHAR(20) DEFAULT NULL COMMENT '手机号' AFTER email;

6.3 在表最前面新增字段

ALTER TABLE user_info
ADD code VARCHAR(32) NOT NULL COMMENT '编码' FIRST;

7. 删除表字段

ALTER TABLE user_info
DROP COLUMN phone;

8. 删除表

DROP TABLE user_info;

如果存在则删除：

DROP TABLE IF EXISTS user_info;

9. 清空表数据

9.1 删除所有数据，保留表结构

DELETE FROM user_info;

9.2 快速清空表

TRUNCATE TABLE user_info;

区别：

  • DELETE FROM 表名;：逐行删除，通常可回滚
  • TRUNCATE TABLE 表名;：速度更快，通常相当于重建表，自增值通常会重置

10. 常见综合示例

示例 1：创建数据库并指定字符集

CREATE DATABASE IF NOT EXISTS mydb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;

示例 2：创建远程用户并授予数据库权限

CREATE USER 'devuser'@'%'
IDENTIFIED WITH caching_sha2_password BY 'StrongPassword123!';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP
ON mydb.* TO 'devuser'@'%';

示例 3：创建兼容旧客户端的用户

CREATE USER 'devuser'@'%'
IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

示例 4：创建可继续授权的用户

CREATE USER 'manager'@'%'
IDENTIFIED BY 'ManagerPassword123!';

GRANT ALL PRIVILEGES ON mydb.* TO 'manager'@'%'
WITH GRANT OPTION;

示例 5：授予 PROCESS 权限

GRANT PROCESS ON *.* TO 'devuser'@'%';

示例 6：创建与 root 类似权限的管理员

CREATE USER 'admin'@'%'
IDENTIFIED WITH mysql_native_password BY 'AdminPassword123!';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

示例 7：创建表并后续修改字段

CREATE TABLE employee (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) DEFAULT 0.00,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

新增字段到 name 后面：

ALTER TABLE employee
ADD age INT DEFAULT 0 AFTER name;

修改字段名和类型：

ALTER TABLE employee
CHANGE name full_name VARCHAR(100) NOT NULL;

修改字段类型：

ALTER TABLE employee
MODIFY salary DECIMAL(12,2) DEFAULT 0.00;

修改表名：

ALTER TABLE employee RENAME TO employee_info;

删除表：

DROP TABLE employee_info;
Leave a Comment