mysql 常用命令

命令行

登录

mysql -u root -p'password' -h 127.0.0.1 -P 端口 --default-character-set=utf8mb4

• u 指定用户名
• p 指定密码
• h 指定 MySQL 服务器地址，默认是 localhost
• P 指定端口
• --default-character-set 设置默认字符集，避免乱码问题

修改字段

-- 修改t_user 表中id的修改字段类型和属性
alter table t_user MODIFY id int not null auto_increment comment 'id';

--- 修改字段名
alter table  table_name CHANGE old_column_name new_column_name data_type attributes;

-- 添加新字段
alter table table_name ADD column_name data_type attributes;

-- 删除字段
alter table table_name DROP column_name;

查询树形显示

select * from t_user /G
-- 显示表信息
show create table t_goods;

命令行执行sql文件

使用 source 命令

1. 首先登录mysql

   mysql -uroot -p
   

2. 在 MySQL 命令行中执行：

   source 文件路径;
   

   或者简写

   ./ 文件路径
   

   示例

   source /path/to/your_script.sql;
   

直接在命令行中执行

不进入 MySQL 交互界面，直接执行：

mysql -u 用户名 -p 数据库名 < 文件路径

示例：

mysql -u root -p mydatabase < /path/to/your_script.sql

使用 --execute 参数

mysql -u 用户名 -p 数据库名 -e "source 文件路径"

示例：

mysql -u root -p mydatabase -e "source /path/to/your_script.sql"

批量执行多个文件

for sql in *.sql; do mysql -u 用户名 -p 数据库名 < "$sql"; done
实用参数说明

索引

使用 ALTER TABLE 语句给现有表添加索引

-- 创建普通索引
ALTER TABLE t_goods ADD INDEX idx_category (category_id);

-- 创建唯一索引
ALTER TABLE t_users ADD UNIQUE INDEX idx_email_unique (email);

-- 创建复合索引
ALTER TABLE t_orders ADD INDEX idx_user_product (user_id, product_id);

-- 删除索引
ALTER TABLE t_goods DROP INDEX idx_category;

-- 查看表的索引
SHOW INDEX FROM t_goods;

分析命令

explain

EXPLAIN SELECT * FROM t_goods WHERE category_id = 10;

输出示例：

关键字段说明：

• type：访问类型（如 ALL, index, range, ref 等）。
  type 是性能优化的重要指标，表示 MySQL 查找表中行的方式：

• key：实际使用的索引。
• rows：查询时预计扫描的行数。
• select_type
  指定查询的类型：

• possible_keys 和 key
  possible_keys：查询可能使用的索引。
  key：实际使用的索引。
  如果 possible_keys 和 key 都为空，说明没有索引可用，可能需要添加索引。
• Extra：额外信息（如 Using index, Using temporary）。

时间函数

# 秒转换为日期
select from_unixtime(sec, format);
# 示例
select FROM_UNIXTIME(1739980800, '%Y-%m-%d %H:%i:%s')

# 日期转秒
select UNIX_TIMESTAMP('2025-02-20 00:00:00');

# 日期格式化 输出：2025-04-10 14:30:00
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

mysql 5.7

字符串转数字

select * from t_user order by convert(power, UNSIGNED) desc ;

json处理

select user_id, difficulty_data from t_user_dungeon where json_extract(difficulty_data, '$[0].id') = 5 order by json_extract(difficulty_data, '$[0].count') desc;

JSON_EXTRACT的使用

如果要从库里只查出一长串json中的某个字段，可以直接使用JSON_EXTRACT进行提取。它可以直接分解JSON并查询，实际上就是在提供的path下查找值。JSON_EXTRACT可以通过键索引访问，也可以通过数字索引访问，示例如下：

#语法：
JSON_EXTRACT(json_doc, path[, path] ...)
#JSON_EXTRACT查询示例，解析整数索引的数组 
select  id,chars,JSON_EXTRACT(chars,"$[0]","$[1]","$[2]","$[1]") as charjson from test where id=2 ;
#查询得到的记录如下：
2|["test0","test1.","2test2."]|["test0", "test1.", "2test2.", "test1."]
#JSON_EXTRACT查询示例，解析键值对的对象 
select  id,chars,JSON_EXTRACT(chars,"$.name","$.mark") as charjson from test where id=3 ;
3|{"id":"12345","name":"04007.cn","mark":"test."}|["04007.cn", "test."]
可见JSON_EXTRACT对json操作的时候有两种方式提取，对于键值对的内容通过键名提取，对于索引的数组使用数字下标提取。新查询得到的内容是默认组成新的数组json。

JSON_ARRAY_APPEND

JSON_EXTRACT是从数据库中的json中提取目标值，而JSON_ARRAY_APPEND则是为实现查询的时候把数据添加进查询结果

#语法：
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
#JSON_ARRAY_APPEND查询示例
select  id,chars,JSON_ARRAY_APPEND(chars,'$[1]',"test3",'$[0]',"test4") as charjson from test where id=2 ;
#执行结果如下：
2 | ["test0","test1."]|[["test0", "test4"], ["test1.", "test3"]]
select  id,chars,JSON_ARRAY_APPEND(chars,'$.name',"hello") as charjson from test where id=3 ;
#执行结果如下：
3 | {"id":"12345","name":"04007.cn"} | {"id": "12345", "name": ["04007.cn", "hello"]}
#也可以执行对$进行操作
select  id,chars,JSON_ARRAY_APPEND(chars,'$',"hello") as charjson from test where id=3 ;
3 | {"id":"12345","name":"04007.cn"} | [{"id": "12345", "name": "04007.cn"}, "hello"]

MYSQL中JSON操作的方法大全

创建json的方法：

• 创建JSON数组形式和JSON对象的数据

  • JSON_ARRAY([val[, val] …])
  • JSON_OBJECT([key, val[, key, val] …])

• 修改json的方法，在JSON数组后增加新的数据

  • JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
  • JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

• 合并JSON数据

  • JSON_MERGE(json_doc, json_doc[, json_doc] …)

• 删除一部分JSON数据

  • JSON_REMOVE(json_doc, path[, path] …)

• 替换一部分JSON数据

  • JSON_REPLACE(json_doc, path, val[, path, val] …)

• 有存在的数据就替换，没有就插入

  • JSON_SET(json_doc, path, val[, path, val] …)

查询json的方法

• 查找是否包含

  • JSON_CONTAINS(json_doc, val[, path])

• 查找path（就是key）是否存在

  • JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
    第二个参数one或者all表示是要全部满足还是只满足一个

    mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
    

• 分解JSON 并查询，实际上就是在提供的path下查找值

  • JSON_EXTRACT(json_doc, path[, path] …)
  • JSON_EXTRACT 还可以使用一种替代语法 column->path直接查询json中的列，示例如下：

  mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g FROM jemp WHERE c->"$.id" > 1  ORDER BY c->"$.name";
  mysql> SELECT c, c->"$.id", g FROM jemp WHERE c->"$.id" > 1  ORDER BY c->"$.name";
  

• 提出当前提供path下的key值

  • JSON_KEYS(json_doc[, path])

• 按着提供的值去查询，返回path数组。one_or_all南

  • JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])

查询JSON自有属性的方法

• 查询当前JSON深度

  • JSON_DEPTH(json_doc)

• 查询当前层级(path)下对象或者数组的元素数量

  • JSON_LENGTH(json_doc[, path])

• 返回JSON值类型

  • JSON_TYPE(json_val)

• 验证是否是JSON

  • JSON_VALID(val)

修改权限

mysql> use mysql;

查看user表下的用户及允许访问的host
目前数据库所有账号的host都设置成localhost，只允许本机访问

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| Him              | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

我们把Him这个账号的host改成%，表示允许所有ip访问

mysql> update user set host='%' where user='Him';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| Him              | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

刷新

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password'

• username：用户名；
• host：指定在哪个主机上可以登录，本机可用localhost，%通配所有远程主机；
• password：用户登录密码；

授权

GRANT privilege_type ON database.table TO 'username'@'host';

• privilege_type：权限类型，常见的权限有：
  • SELECT：查询权限。
  • INSERT：插入数据权限。
  • UPDATE：更新数据权限。
  • DELETE：删除数据权限。
  • ALL PRIVILEGES：授予所有权限。
  • CREATE、DROP、ALTER：管理表结构的权限。
  • INDEX：创建索引的权限。
  • EXECUTE：执行存储过程的权限。
  • GRANT OPTION：允许用户授予自己拥有的权限给其他用户。
• database：数据库名称，可以是 * 表示所有数据库。
• table：表名称，可以是 * 表示所有表。如果是所有数据库的所有表，则用 .。
• 'username'@'host'：授权的用户。

示例：授予john 用户对所有数据库的查询权限

GRANT SELECT ON *.* TO 'john'@'localhost';

示例：授予 john 用户对某个数据库的所有权限

GRANT ALL PRIVILEGES ON `my_database`.* TO 'john'@'localhost';

示例：授予 john 用户对特定表的更新和删除权限

GRANT UPDATE, DELETE ON `my_database`.`my_table` TO 'john'@'localhost';

刷新

执行 GRANT 命令后，权限会立即生效。但是，如果权限表发生更改，最好通过 FLUSH PRIVILEGES 来确保权限表刷新，尤其是在直接修改权限表时。

flush privileges;

查看权限

你可以使用 SHOW GRANTS 命令查看用户的权限。这个命令会列出用户在数据库中的所有权限。

查看某个用户的权限：

SHOW GRANTS FOR 'john'@'localhost';

# response
GRANT SELECT ON *.* TO 'john'@'localhost'
GRANT ALL PRIVILEGES ON `my_database`.* TO 'john'@'localhost'

撤销权限

撤销用户权限时，可以使用 REVOKE 语句。

撤销权限语法：

REVOKE privilege_type ON database.table FROM 'username'@'host';

示例：撤销 john 用户对某个表的查询权限

REVOKE SELECT ON `my_database`.`my_table` FROM 'john'@'localhost';

示例：撤销 john 用户对所有数据库的所有权限

REVOKE ALL PRIVILEGES ON *.* FROM 'john'@'localhost';

撤销权限后，同样需要刷新权限表以使更改生效：

FLUSH PRIVILEGES;

删除用户

如果不再需要某个用户，可以使用 DROP USER 删除该用户。

删除用户语法：

DROP USER 'username'@'host';

示例：

DROP USER 'john'@'localhost';

权限的继承和 GRANT OPTION

GRANT OPTION 权限允许用户将其拥有的权限授予其他用户。这意味着用户不仅可以访问某个数据库的资源，还可以将自己的权限转授给其他用户。

示例：授予 john 用户对其他用户的授权权限：

GRANT SELECT, INSERT ON `my_database`.* TO 'john'@'localhost' WITH GRANT OPTION;

这条命令授予了john 用户 my_database 数据库上的查询和插入权限，并允许 john 将这些权限授予其他用户。

授予 SUPER 权限

有些权限需要超级权限（SUPER），例如改变服务器的全局设置、停止查询、杀死线程等。授予超级权限时，可以使用：

GRANT SUPER ON *.* TO 'username'@'host';

使用 *.* 来授予所有数据库的权限

如果你想授予某个用户对所有数据库的权限，可以使用 *.* 来表示所有数据库和表。

示例：授予用户对所有数据库的所有权限

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

权限与主机限制

MySQL 中的权限是与主机绑定的，即同一个用户名在不同的主机下可能有不同的权限。例如，'john'@'localhost' 和 'john'@'%' 是不同的权限配置，前者表示该用户只能从本地连接，后者表示该用户可以从任何主机连接。

示例：授予 john 用户从任何主机连接的权限

GRANT ALL PRIVILEGES ON *.* TO 'john'@'%';

总结

• 使用 CREATE USER 创建用户。
• 使用 GRANT 授予权限，可以选择特定的数据库、表或全局权限。
• 使用 REVOKE 撤销权限。
• SHOW GRANTS 可查看用户的权限。
• DROP USER 删除用户。
• 使用 FLUSH PRIVILEGES 刷新权限表，使更改立即生效。

通过这些命令，你可以灵活地为不同的用户分配适当的权限，确保 MySQL 数据库的安全性和可控性

常用配置查询命令

docker run mysql

docker run -d \
  -p 3306:3306 \
  --name mysql-container \
  -v /path/to/my.cnf:/etc/mysql/my.cnf \
  -e MYSQL_ROOT_PASSWORD=rootpassword \
  mysql:8.0

mysql默认加载配置路径

mysql --help | grep "Default options" -A 1
# response
Default options are read from the following files in the given order:
  /etc/mysql/my.cnf /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/conf.d/

在mysql中查看bind_address

mysql -uroot -p
# 进入后
SHOW VARIABLES LIKE 'bind_address';

#response
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| bind_address     | 0.0.0.0   |
+------------------+-----------+

实用命令

# 统计每日注册人数
select date_format(register_time, '%Y-%m-%d'), count(id) from `puppy-wechat1`.t_user group by date_format(register_time, '%Y-%m-%d');


select from_unixtime(create_time, '%Y-%m-%d'), sum(price) from `puppy-center`.t_order where state=2 and from_unixTime(create_time) > '2025-04-10'  group by from_unixtime(create_time, '%Y-%m-%d') ;