mysql

原创 已于 2025-07-03 11:39:11 修改 · 947 阅读 · 1 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#mysql #数据库
于 2022-10-27 11:26:59 首次发布
本文介绍MySQL中的高级查询技巧，如GROUP_CONCAT函数的多种用法、JSON数据查询及处理，以及如何排查和解决死锁问题，包括查看未提交事务的状态和通过日志定位问题。

文章目录

语法

GROUP_CONCAT

GROUP_CONCAT的作用是将结果结合成字符串，
示例：
除了名字不一样，其它都一样，现在需要将名字逗号拼接
在这里插入图片描述

SELECT
    v.vehicle_no,
    GROUP_CONCAT(d.driver_name ORDER BY d.driver_name SEPARATOR ', ') AS driver_names,
		CONCAT(m.brand,m.model),
-- 如果这个值是空的，显示为否
    IFNULL(v.register_date, "否") AS register_date,		
-- 值判断，switch效果
    CASE 
        WHEN v.vehicle_type = 1 THEN "小型普通客车"
        WHEN v.vehicle_type = 2 THEN "小型轿车"
        WHEN v.vehicle_type = 3 THEN "小型越野客车"
    END AS vehicle_type
FROM
    cc_vehicle v
LEFT JOIN cc_vehicle_driver d ON v.id = d.vehicle_id
LEFT JOIN cc_vehicle_model m ON m.id = v.model_id
GROUP BY
    v.id, v.vehicle_no, m.brand, m.model, v.register_date, v.vehicle_type

效果：
在这里插入图片描述

默认用逗号分隔

select GROUP_CONCAT(user_name) from score1

在这里插入图片描述

指定分隔符号

select GROUP_CONCAT(user_name separator ';') from score1

在这里插入图片描述

结果去重

select GROUP_CONCAT(distinct user_name) from score1

在这里插入图片描述

倒序排列

select GROUP_CONCAT(user_name ORDER BY user_name desc) from score1

在这里插入图片描述

按照名称分组

select GROUP_CONCAT(user_name) from score1 GROUP BY user_name

在这里插入图片描述

注意事项

1. 长度限制（group_concat_max_len）
  • MySQL 默认限制拼接结果长度（默认 1024 字节 ，可通过 SHOW VARIABLES LIKE 'group_concat_max_len'; 查看）。
  • 若拼接内容超长，结果会被 截断 ！解决方法：
    -- 临时生效（当前会话）
SET SESSION group_concat_max_len = 102400;  
-- 永久生效（需修改配置文件，重启服务）
# 在 my.cnf 或 my.ini 中添加  
group_concat_max_len = 102400
2. 性能影响
  • 处理 超大量数据 时，拼接操作会消耗较多内存和 CPU，可能拖慢查询。
  • 优化建议：
    • 尽量缩小查询范围（用 WHERE 过滤不必要数据）；
    • 避免嵌套复杂表达式，简化拼接逻辑；
    • 确认是否真的需要合并所有数据，而非分开展示。

cast

CAST函数语法规则是：Cast(字段名 as 转换的类型 )，其中类型可以为：

CHAR[(N)] 字符型
DATE 日期型
DATETIME 日期和时间型
DECIMAL float型
SIGNED int
TIME 时间型
把data字段转为signed类型

select cast(date as signed) as date from  table1;

left

LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分，
从左往右返回2位长度字符

select left('张三李四',2)

在这里插入图片描述

时间相关函数

  • NOW()
    获取当前时间
select NOW() 当前时间和时间,CURDATE() 当前日期,CURTIME() 当前时间 FROM dual

在这里插入图片描述

DATE_SUB

从日期减去指定的时间间隔DATE_SUB(date,INTERVAL expr type)

  • 三个月内的数据
    当前时间往前三个月内的数据
SELECT *
FROM your_table
WHERE date_column >= DATE_SUB(NOW(), INTERVAL 3 MONTH);

DATE_ADD

从日期增加指定的时间间隔DATE_ADD(date,INTERVAL expr type)

  • 未来三个月内的数据
    当前时间未来三个月内的数据
SELECT *
FROM your_table
WHERE date_column >= NOW()
  AND date_column < DATE_ADD(NOW(), INTERVAL 3 MONTH);

DATE

DATE函数，将日期转为年月日DATE('2024-11-6 15:05:05')=2024-11-6

select * from user where DATE(create_time)=DATE('2024-11-6 15:05:05')

IF

判断a是否等于b，相等返回true，不相等返回fasle

select if(a=b,'true','false') result from dual

死锁

information_schema.INNODB_TRX

需要管理员权限

SELECT * FROM information_schema.INNODB_TRX

在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务：

KILL 165667

show status like 'innodb_row_lock%'

Innodb_row_lock_current_waits : 当前等待锁的数量
Innodb_row_lock_time : 系统启动到现在，锁定的总时间长度
Innodb_row_lock_time_avg : 每次平均锁定的时间
Innodb_row_lock_time_max : 最长一次锁定时间
Innodb_row_lock_waits : 系统启动到现在总共锁定的次数

+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |#事务ID
| trx_state | varchar(13) | NO | | | |#事务状态：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 ||#事务开始时间；
| trx_requested_lock_id | varchar(81) | YES | | NULL ||#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |#事务的权重，反应一个事务修改和锁定的行数，当发现死锁需要回滚时，权重越小的值被回滚
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 ||#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL ||#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 ||#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 ||#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 ||#事务锁住的内存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 ||#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 ||#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 ||#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL ||#最后的外键错误
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 ||#

暴力解决
KILL 165667 ; KILL 后面的数字指的是 trx_mysql_thread_id 值

trx_mysql_thread_id：MySQL中的进程ID，与show processlist中的ID值相对应
trx_query：事务运行的SQL语句

死锁表

select * from performance_schema.data_locks;

+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |#锁ID
| lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
| lock_mode | varchar(32) | NO | | | |#锁模式
| lock_type | varchar(32) | NO | | | |#锁类型
| lock_table | varchar(1024) | NO | | | |#被锁的表
| lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
| lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
| lock_data | varchar(8192) | YES | | NULL | |#被锁的数据

# 查询死锁等待时间
select * from sys.innodb_lock_waits;

locked_table : 哪张表出现的等待

waiting_trx_id: 等待的事务(与上个视图trx_id 对应)

waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id)

blocking_trx_id : 当前拥有锁的事务ID

blocking_pid : 当前拥有锁的锁ID

死锁的排查

未提交的事务

processlist中未提交的事务
show processlist

根据INNODB_TRX中的trx_mysql_thread_id查询进程明细，host为客户端ip段进口

SELECT * FROM information_schema.processlist where id=4086

在这里插入图片描述

未提交事务的Command为Sleep，State为空，Info为NULL

information_schema.innodb_trx中的未提交事务

information_schema.innodb_trx.trx_query为NULL，无法提供未提交事务的SQL语句

SELECT * FROM information_schema.INNODB_TRX

在这里插入图片描述

performance_schema.events_statements_current中的未提交事务
select * from performance_schema.events_statements_current 
where sql_text not like 'select * from performance_schema.events_statements_current%'

一个事务可能有一组sql组成，这个方法只能看到事务最后执行的是什么SQL，无法看到全部。可以通过performance_schema.threads表来关联，将information_schema.processlist和performance_schema.events_statements_current一一对应起来

select t1.id, t2.thread_id, t3.sql_text from information_schema.processlist t1,performance_schema.threads t2,performance_schema.events_statements_current t3
where t1.id=4806 and t1.id=t2.processlist_id and t2.thread_id = t3.thread_id

如果没有死锁，并且状态一直是running，running了很久，可能是事物没有设置回滚，

select * from information_schema.`PROCESSLIST` where id in (SELECT trx_mysql_thread_id from information_schema.INNODB_TRX where trx_state='RUNNING')

在这里插入图片描述

事务一直running

#查看日志状态
show variables like 'general_log';
#设置是否开启日志
set global general_log=off;
#设置日志记录方式 table、file
set global log_output='table'
#根据thread_id查sql
SELECT *,CONVERT (argument USING utf8) as `sql` from mysql.general_log where thread_id = 17 ORDER BY event_time DESC;
# 清空日志表
truncate table mysql.general_log

死锁日志

show engine innodb status

# 这行表示事务id为3105550，事务处于活跃状态9s，starting index read表示正在使用索引读取数据行
---TRANSACTION 3105550, ACTIVE 9 sec starting index read
#这行表示该事务正在使用1个表，且涉及锁的表有1个
mysql tables in use 1, locked 1
# 这行表示在等待3把锁，占用内存1136字节，涉及1行记录
LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 22
# 这行表示该事务的线程ID信息，操作系统句柄信息，连接来源、用户
MySQL thread id 5809, OS thread handle 140507283683072, query id 3017641 192.168.0.0.1 ztb_gansu updating
# 这行表示事务执行的最后一条SQL信息
update yw_bd set ab=null, address=null, aqwmcsf=null, bdcode='BD2022001875', bdfl=null, bdname='zzzzzz', bgplabdid=null, bjfs=null, bzjjnfs='', bzjkhh='', bzjyh='', bzjzh='', cbjsj=null, cgdwlxdh='18100001111', cgdwlxr='1', cgdwztid='1', cgdwztname='1', cglx=null, cgpmcodes=null, cgpmnames=null, cjryhl=null, content=null, cretime=null, currency=null, extinfo='{"extZjsjYear":{"remark":"最近一个数据审计年","value":"2022"},"extSfsjssgsgy":{"remark":"是否涉及上市公司国有股权间接转让","value":"0"},"extSfcztxqlr":{"remark":"是否存在他项权利人","value":"1"},"extJzrsj
Trx read view will not see trx with id >= 3105550, sees < 3100092
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
# 这行信息表示等待的锁是一个record lock，空间id是503 ，页编号为64 ，大概位置在页的80位处，锁发生在表ztb_gansu.test_tb的yw_bd索引上，是一个X锁，但是不是gap lock，waiting表示正在等待锁
RECORD LOCKS space id 503 page no 64 n bits 80 index PRIMARY of table `ztb_gansu`.`yw_bd` trx id 3105550 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 96; compact format; info bits 0
 0: len 30; hex 613935616661346235393365343034336135316230373337383764323039; asc a95afa4b593e4043a51b073787d209; (total 32 bytes);
 1: len 6; hex 0000002f589e; asc    /X ;;
 2: len 7; hex 02000000ee23e7; asc      # ;;
 3: len 30; hex 643162323764333863633465343935656139316230353930396131623463; asc d1b27d38cc4e495ea91b05909a1b4c; (total 32 bytes);
 4: SQL NULL;
 5: len 5; hex 99ae374146; asc   7AF;;
 6: len 7; hex 61616161616161; asc aaaaaaa;;
 7: len 12; hex 424432303232303031383735; asc BD2022001875;;
 8: len 11; hex 8000000000000001000000; asc            ;;
 9: SQL NULL;
 10: SQL NULL;
 11: len 0; hex ; asc ;;
 12: len 0; hex ; asc ;;
 13: len 1; hex 31; asc 1;;
 14: len 11; hex 3138313030303031313131; asc 18100001111;;
 15: SQL NULL;
 16: SQL NULL;
 17: SQL NULL;
 18: SQL NULL;
 19: len 1; hex 30; asc 0;;
 20: SQL NULL;
 21: SQL NULL;
 22: SQL NULL;
 23: SQL NULL;
 24: SQL NULL;
 25: SQL NULL;
 26: SQL NULL;
 27: SQL NULL;
 28: SQL NULL;
 29: SQL NULL;
 30: SQL NULL;
 31: SQL NULL;
 32: SQL NULL;
 33: SQL NULL;
 34: SQL NULL;
 35: SQL NULL;
 36: SQL NULL;
 37: SQL NULL;
 38: len 1; hex 31; asc 1;;
 39: len 1; hex 31; asc 1;;
 40: SQL NULL;
 41: SQL NULL;
 42: SQL NULL;
 43: SQL NULL;
 44: SQL NULL;
 45: SQL NULL;
 46: SQL NULL;
 47: SQL NULL;
 48: SQL NULL;
 49: SQL NULL;
 50: SQL NULL;
 51: SQL NULL;
 52: SQL NULL;
 53: SQL NULL;
 54: SQL NULL;
 55: SQL NULL;
 56: SQL NULL;
 57: SQL NULL;
 58: SQL NULL;
 59: SQL NULL;
 60: SQL NULL;
 61: SQL NULL;
 62: SQL NULL;
 63: SQL NULL;
 64: SQL NULL;
 65: SQL NULL;
 66: SQL NULL;
 67: SQL NULL;
 68: SQL NULL;
 69: SQL NULL;
 70: SQL NULL;
 71: SQL NULL;
 72: SQL NULL;
 73: SQL NULL;
 74: SQL NULL;
 75: SQL NULL;
 76: SQL NULL;
 77: SQL NULL;
 78: SQL NULL;
 79: SQL NULL;
 80: SQL NULL;
 81: SQL NULL;
 82: len 1; hex 31; asc 1;;
 83: SQL NULL;
 84: SQL NULL;
 85: len 11; hex 8000000000000001000000; asc            ;;
 86: len 1; hex 30; asc 0;;
 87: SQL NULL;
 88: len 4; hex 46584a4a; asc FXJJ;;
 89: len 3; hex 4f4e45; asc ONE;;
 90: SQL NULL;
 91: SQL NULL;
 92: len 0; hex ; asc ;;
 93: len 0; hex ; asc ;;
 94: SQL NULL;
 95: len 30; hex 002900170c23010600290106002f010700360107003d010700440107004b; asc  )   #   )   /   6   =   D   K; (total 3096 bytes);

------------------
# 事物3103156，活跃2619s
---TRANSACTION 3103156, ACTIVE 2619 sec
# 等待1把锁，内存1136，涉及一行数据
1 lock struct(s), heap size 1136, 1 row lock(s)
# 线程id 5804,以及ip和用户
MySQL thread id 5804, OS thread handle 140510655903488, query id 2996772 192.168.0.0.1 root
---TRANSACTION 3103114, ACTIVE 2660 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5796, OS thread handle 140507265332992, query id 2996402 192.168.0.0.1 root
---TRANSACTION 3101888, ACTIVE 3665 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5776, OS thread handle 140510624380672, query id 2987217 192.168.0.0.1 root
---TRANSACTION 3101267, ACTIVE 4349 sec
1 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5770, OS thread handle 140519116973824, query id 2989155 192.168.0.0.1 root
---TRANSACTION 3101247, ACTIVE 4375 sec
1 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5769, OS thread handle 140507283977984, query id 2981788 192.168.0.0.1 root
---TRANSACTION 3100092, ACTIVE 5668 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 5749, OS thread handle 140520111859456, query id 2971455 192.168.0.0.1 root

错误

zhangsan这个用户没有user表的权限

SELECT command denied to user 'zhangsan'@'192.168.0.79' for table 'user'

查看sql记录

一、mysql查看正在执行的sql语句

show processlist;

二、mysql查看已经执行的历史sql语句（方法：开启日志模式）

-- 查看日志功能设置状态
show variables like 'general_log'; 
-- 查看当前日志输出类型：默认file
show variables like 'log_output';

-- 打开日志记录功能
set global general_log=on; 
-- 关闭日志记录功能
set global general_log=off; 

-- 设置日志输出至table
set global log_output='table';

-- 日志输出至table模式，查看日志记录
SELECT * from mysql.general_log ORDER BY event_time DESC;

-- 设置日志输出至file
set global log_output='file'; 

-- 查看日志输出文件的保存路径
show variables like 'general_log_file';

-- 修改日志输出文件的保存路径
set global general_log_file='tmp/general.log'; 

-- 日志输出至table模式，清空日志记录
truncate table mysql.general_log;

-- 日志输出至file模式，查看日志记录
cat /tmp/general.log

默认文件位置：/home/root/mysql/data/mysql/general_log.CSV

注意：在查询到所需要的记录之后，应尽快关闭日志模式，占用磁盘空间比较大

查看当前数据库的连接数量

最大连接数量

show VARIABLES like 'max_connections'

在这里插入图片描述

当前连接数

SHOW STATUS LIKE 'Threads_connected';

在这里插入图片描述
断开一个后查看
在这里插入图片描述

连接信息

show status like ‘%connect%’;
在这里插入图片描述

查询当前所有连接

show full processlist;
在这里插入图片描述
SHOW FULL PROCESSLIST 是 MySQL 中一个非常有用的命令，用于显示当前正在运行的所有线程及其详细信息。这个命令可以帮助你诊断数据库性能问题、识别长时间运行的查询以及了解数据库的当前活动状态。

命令语法

SHOW FULL PROCESSLIST;

输出字段解释

执行 SHOW FULL PROCESSLIST 命令后，你会看到一个包含多个字段的结果集。以下是各个字段的详细解释：

  1. Id：

    • 描述：每个连接的唯一标识符。
    • 用途：用于标识特定的连接，可以用于 KILL 命令终止某个连接。

  2. User：

    • 描述：执行查询的用户名。
    • 用途：帮助你了解哪个用户正在执行查询。

  3. Host：

    • 描述：客户端的主机名或 IP 地址及端口号。
    • 用途：帮助你了解查询是从哪个客户端发起的。

  4. db：

    • 描述：当前连接正在使用的数据库名称。
    • 用途：帮助你了解查询是在哪个数据库中执行的。

  5. Command：

    • 描述：当前连接的状态或命令类型。
    • 常见值：
      • Sleep：连接处于空闲状态。
      • Query：正在执行查询。
      • Connect：正在建立连接。
      • Init DB：初始化数据库。
      • Field List：请求字段列表。
      • Prepare：准备执行 SQL 语句。
      • Execute：执行 SQL 语句。
      • Close stmt：关闭准备好的语句。
      • Fetch：获取结果集的一部分。
      • Ping：检查连接是否仍然有效。
      • Quit：断开连接。
      • Daemon：后台线程。

  6. Time：

    • 描述：连接处于当前状态的时间（以秒为单位）。
    • 用途：帮助你识别长时间运行的查询。

  7. State：

    • 描述：当前连接的详细状态。
    • 常见值：
      • starting：连接刚刚开始。
      • checking permissions：检查权限。
      • Opening tables：打开表。
      • System lock：等待系统锁。
      • init：初始化查询。
      • updating：更新数据。
      • query end：查询结束。
      • freeing items：释放资源。
      • cleaning up：清理连接。
      • Sending data：发送数据。
      • Sorting result：排序结果。
      • Writing to net：写入网络。
      • end：结束。

  8. Info：

    • 描述：当前执行的 SQL 语句。
    • 用途：帮助你了解具体的查询内容。
  • 使用场景

  1. 识别长时间运行的查询：

    • 查找 Time 字段值较大的行，这些行可能是导致性能问题的原因。

  2. 终止特定连接：

    • 使用 KILL 命令终止特定的连接。例如，终止 ID 为 2 的连接：
      KILL 2;

  3. 监控数据库活动：

    • 定期运行 SHOW FULL PROCESSLIST 命令，监控数据库的活动情况，确保没有异常的查询或连接。

  4. 调试查询：

    • 查看 Info 字段中的 SQL 语句，帮助你调试和优化查询。

注意事项

  • 权限：只有具有 PROCESS 权限的用户才能看到所有连接的信息。普通用户只能看到自己的连接。
  • 性能影响：频繁执行 SHOW FULL PROCESSLIST 可能会对数据库性能产生轻微影响，因此建议在必要时使用。

查看连接创建的时间

并没有直接显示创建连接的字段，需要当前时间减去运行时长，得到创建时间

				SELECT 
    Id,
    User,
    Host,
    db,
    Command,
    Time,
    State,
    Info,
    CURRENT_TIMESTAMP() - INTERVAL Time SECOND AS Connect_Time
FROM 
    INFORMATION_SCHEMA.PROCESSLIST;

在这里插入图片描述

查看表信息

查询所有表信息

SELECT 
    *
FROM 
    INFORMATION_SCHEMA.TABLES

查看表名和表注释

查看表名包括user的表名和注释

SELECT 
    TABLE_NAME AS '表名',
    TABLE_COMMENT AS '表注释'
FROM 
    INFORMATION_SCHEMA.TABLES
where table_name like '%user%'

查看字段信息

SELECT 
    column_name '字段名',
	column_comment '字段注释'
FROM 
    INFORMATION_SCHEMA.COLUMNS
where table_name = '表名'

数据格式为JSON

在这里插入图片描述

JSON_EXTRACT查询

查询json中code为4的数据，需要注意的是4如果为int类型，就不能加引号

select * from user where  JSON_EXTRACT(param,"$.code") = 4 and JSON_EXTRACT(param,"$.desc") like '%全%'

还有一种简化的写法

select * from user where  param ->"$.code" = 4 and param -> "$.desc" like '%全%'

多条件用and拼接，精确匹配用=，模糊用like
在这里插入图片描述
还有一种简化的写法

select * from user where  param ->"$.code" = 4 and param -> "$.desc" like '%全%'

JSON_CONTAINS查询

JSON_OBJECT就是将里面的参数组成对象形式
JSON_CONTAINS会将JSON型的数组也查询

select * from user where JSON_CONTAINS(param, JSON_OBJECT('code',4))

在这里插入图片描述

  • 满足这个json的code=1,desc=行驶证到期，才能查到
select * from user where JSON_CONTAINS(param, JSON_OBJECT('code',1,'desc','行驶证到期'))

在这里插入图片描述

  • 这个数组必须同时包含code=1和code=2
select * from user where JSON_CONTAINS(param, JSON_OBJECT('code',1)) and JSON_CONTAINS(param, JSON_OBJECT('code',2))

在这里插入图片描述

  • 模糊查询
    • 只查询数组，数组中，所有json只要desc模糊包含全的数据
      select * from user where param -> '$[*].desc' like '%全%'
      在这里插入图片描述
    • 查询数组，下标为0的（普通json也算下标为0），desc模糊包含全的数据
      select * from user where param -> '$[0].desc' like '%全%'
      在这里插入图片描述

查询多层嵌套

查询json中，第一层包含desc and desc中包含msg and msg包含code的数据

SELECT *
FROM user
WHERE JSON_EXTRACT(param, '$.desc.msg.code')

在这里插入图片描述
查询json中，第一层为desc and desc中包含msg and msg包含code，并且code=5
的数据

SELECT *
FROM user
WHERE JSON_EXTRACT(param, '$.desc.msg.code') = 5;

在这里插入图片描述

模糊查询多层级

select * from user where param-> '$**.code' like '%4%'

在这里插入图片描述

查询指定key对应的value

查询key为platform的value值

SELECT 
    JSON_EXTRACT(u.param, '$[*].platform') AS platform_values
FROM user u where JSON_EXTRACT(u.param, '$[*].platform') is not null

在这里插入图片描述

$[]和$

$[*]代表筛选json型的数组，key的值为platform的数据

select * from user where JSON_EXTRACT(param, '$[*].platform')

$[0]代表筛选json型的数组，下标为0的，key的值为platform的数据

select * from user where JSON_EXTRACT(param, '$[0].platform')

$代表筛选json型的，key的值为code的数据

select * from user where JSON_EXTRACT(param, '$.code')

JSON_MERGE_PATCH

这个函数用来新增JSON数据中的字段，而不影响其它字段。
如果键名相同，会覆盖原值，如果键名不相同，则会新增到json中

  • 示例

    SELECT JSON_MERGE_PATCH('{"name": "Alice", "age": 30, "city": "Beijing"}', '{"age": 35, "country": "China"}') AS merged_json;

    结果

    {"age": 35, "city": "Beijing", "name": "Alice", "country": "China"}

JSON_SET

修改指定key的值

SELECT JSON_SET('{"name": "Alice", "age": 30}', '$.age', 35) AS set_age;

age改为35
在这里插入图片描述

JSON_REMOVE

移除json中指定的值

select JSON_REMOVE('{"Flag": 1, "State": 0}', '$.Flag')

移除Flag
在这里插入图片描述

运算符、符号、函数

1. 算术运算符

符号描述示例
+加法SELECT 5 + 3;
-减法SELECT 5 - 3;
*乘法SELECT 5 * 3;
/除法SELECT 5 / 3;
%取模（取余数）SELECT 5 % 3;

2. 比较运算符

符号描述示例
=等于SELECT * FROM users WHERE id = 1;
<> 或 !=不等于SELECT * FROM users WHERE id != 1;
<小于SELECT * FROM users WHERE age < 30;
>大于SELECT * FROM users WHERE age > 30;
<=小于或等于SELECT * FROM users WHERE age <= 30;
>=大于或等于SELECT * FROM users WHERE age >= 30;
BETWEEN在两个值之间SELECT * FROM users WHERE age BETWEEN 20 AND 30;
IN在指定的集合中SELECT * FROM users WHERE id IN (1, 2, 3);
LIKE模糊匹配SELECT * FROM users WHERE name LIKE 'A%';
IS NULL是否为空SELECT * FROM users WHERE address IS NULL;

3. 逻辑运算符

符号描述示例
AND逻辑与SELECT * FROM users WHERE age > 20 AND age < 30;
OR逻辑或SELECT * FROM users WHERE age < 20 OR age > 30;
NOT逻辑非SELECT * FROM users WHERE NOT age = 25;

4. 聚合函数

虽然严格来说不是符号，但聚合函数是查询中常用的工具。

函数描述示例
COUNT()计数SELECT COUNT(*) FROM users;
SUM()求和SELECT SUM(salary) FROM employees;
AVG()平均值SELECT AVG(salary) FROM employees;
MAX()最大值SELECT MAX(salary) FROM employees;
MIN()最小值SELECT MIN(salary) FROM employees;

5. 字符串函数

函数描述示例
CONCAT()连接字符串SELECT CONCAT(first_name, ' ', last_name) FROM users;
SUBSTRING()提取子字符串SELECT SUBSTRING(name, 1, 3) FROM users;
LENGTH()获取字符串长度SELECT LENGTH(name) FROM users;
UPPER()将字符串转换为大写SELECT UPPER(name) FROM users;
LOWER()将字符串转换为小写SELECT LOWER(name) FROM users;
TRIM()去除首尾空格SELECT TRIM(name) FROM users;
REPLACE()替换字符串中的子串SELECT REPLACE(email, 'old.com', 'new.com') FROM users;
RIGHT()提取最右边的字符SELECT RIGHT(phone, 4) FROM contacts;
LEFT()提取最左边的字符SELECT LEFT(phone, 3) FROM contacts;

6. 日期和时间函数

函数描述示例
NOW()返回当前日期和时间SELECT NOW();
CURDATE()返回当前日期SELECT CURDATE();
CURTIME()返回当前时间SELECT CURTIME();
DATE()提取日期部分SELECT DATE(order_date) FROM orders;
TIME()提取时间部分SELECT TIME(order_time) FROM orders;
YEAR()提取年份SELECT YEAR(order_date) FROM orders;
MONTH()提取月份SELECT MONTH(order_date) FROM orders;
DAY()提取日SELECT DAY(order_date) FROM orders;
DATEDIFF()计算两个日期之间的天数差SELECT DATEDIFF(date1, date2) FROM dates;

7. 其他符号

符号描述示例
*所有列SELECT * FROM users;
,列分隔符SELECT id, name FROM users;
.表达式中的属性访问SELECT users.name FROM users;
AS别名SELECT name AS full_name FROM users;
()分组表达式SELECT (price * quantity) AS total FROM orders;

FIND_IN_SET函数

某个值是否存在于一个逗号分隔的字符串列表中，并返回其位置（索引从 1 开始）。如果不存在，返回 0。

  • 语法
FIND_IN_SET(待查找的值, 逗号分隔的字符串)
1. 基础用法：判断元素是否存在

假设有表 users，hobbies 字段存储用户爱好（逗号分隔）：

idnamehobbies
1小明篮球,足球,游泳
2小红唱歌,绘画

查询爱好包含“足球”的用户：

SELECT * 
FROM users 
WHERE FIND_IN_SET('足球', hobbies) > 0;  -- 返回位置大于0表示存在

结果：

idnamehobbies
1小明篮球,足球,游泳
2. 结合聚合函数：统计出现次数

统计所有用户中“游泳”作为爱好的人数：

SELECT 
  COUNT(*) AS swim_count
FROM users 
WHERE FIND_IN_SET('游泳', hobbies) > 0;
3. 模糊匹配陷阱（重点！）

错误示例：查找包含 '1' 的记录：

-- 假设 tags 字段值为 '10,20,30'
SELECT * 
FROM posts 
WHERE FIND_IN_SET('1', tags) > 0;  -- 结果为0（不匹配）

原因：FIND_IN_SET 严格匹配 完整元素，而非子串。'1' 与 '10' 是不同元素。

其它

随机四位整数

介于1000到9999之间

SELECT FLOOR(1000 + RAND() * 9000) AS random_number from dual;
  • 金额处理
    数据库存储类型为int型，单位分，除以100，就是元，
    直接除的话是小数点后4位
select 1200/100 from dual

在这里插入图片描述
保留两位

select FORMAT(1200/100,2) from dual

在这里插入图片描述

计算两个时间差异

SELECT * FROM oc_wyc_order 
WHERE TIMESTAMPDIFF(MINUTE, create_time, NOW()) <= 30;

TIMESTAMPDIFF(MINUTE, create_time, NOW())：
如果create_time小于now，就是正数，否则负数
TIMESTAMPDIFF 是一个 MySQL 函数，用于计算两个日期/时间之间的差异。
MINUTE 参数指定了计算的时间单位为分钟。
create_time 是表中创建时间的列名。
NOW() 是一个 MySQL 函数，返回当前的日期和时间。
条件表示计算的结果必须小于等于30分钟。

手动事务

-- 开启事务
START TRANSACTION;

-- 或者使用 BEGIN 替代 START TRANSACTION
-- BEGIN;

-- 执行你的SQL操作
UPDATE `user`
SET `sex` = '男'

-- 假设还有其他的操作也可以在这里添加

-- 如果一切正常，提交事务
COMMIT;

-- 如果出现错误或需要撤销更改，则回滚事务
-- ROLLBACK;

only_full_group_by

MySQL 中的一个 SQL 模式,判断sql的group是否正确，如果打开了这个严格模式，那么group中必须包含除了非聚合函数外所有select中的列，如果不包含，就会报错。如果关闭了，group就不会做校验

  • 查询是否打开
--- 查询当前会话
SELECT @@GLOBAL.sql_mode;
--- 查询全局配置
SELECT @@SESSION.sql_mode;
  • 打开
--- 当前会话
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
--- 全局
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
  • 关闭
    就是把上面的打开中的ONLY_FULL_GROUP_BY删掉执行一遍就好。
  • 测试
    • sql
      SELECT call_type, id 
	FROM oc_wyc_order 
	GROUP BY call_type;
    • 打开的效果
      打开了严格模式，如果没包含所有非聚合函数的值，就会报错
      在这里插入图片描述
    • 关闭的效果
      在这里插入图片描述
SELECT * FROM information_schema.innodb_trx
没有伞的孩子只能快跑，如若不跑，只有一个结果：超过你的人回头鄙视你
10-27 2555
SELECT * FROM information_schema.innodb_trx命令是用来查看当前运行的所以事务： 在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务： KILL165667; KILL 后面的数字指的是trx_mysql_thread_id值。 ...
MySQL性能调优实战：如何用processlist快速定位慢查询（附常见STATE解析）
weixin_30505225的博客
03-30 379
本文详细介绍了如何利用MySQL的`processlist`功能快速定位慢查询问题，包括基础查询方法、关键STATE解析及优化策略。通过实战案例和自动化监控方案，帮助开发者和DBA高效诊断数据库性能瓶颈，提升系统响应速度。
MySQL系统表information_schema.INNODB_TRX详解及查看当前运行事务
oschina_41731918的博客
11-23 1万+
在日常管理数据库的过程中，有时需要查询MySQL数据库是否正在有正在执行的事务，便于排查业务问题。MySQL的系统库表有数据维护对应的信息，就在。表，包含事务中是否存在锁，事务开启时间，事务执行的语句等等。
information_schema.processlist 表详解
坚定的小辣鸡在努力
02-26 2059
MySQL的information_schema.processlist表（或SHOW PROCESSLIST命令）是监控数据库连接的重要工具。该表包含8个关键字段：连接ID、用户、主机地址、当前数据库、执行状态（如Query/Sleep）、持续时间、SQL执行状态（如Locked/Sending data）以及执行的SQL语句。通过这些字段，管理员可以实时查看所有连接情况，识别慢查询（TIME>10秒）、空闲连接（COMMAND='Sleep'）、锁等待（STATE含lock）等问题，并可终止特定连接
mysql SELECT * FROM information_schema.innodb_trx;
David_jiahuan的博客
06-12 1万+
SELECT * FROM information_schema.innodb_trx命令是用来查看当前运行的所以事务： 在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务： KILL 165667 ; KILL 后面的数字指的是trx_mysql_thread_id值。 ...
information_schema.INNODB_TRX表详解
CaptHua的博客
08-18 1万+
information_schema.INNODB_TRX表；MySQL查看事务；事务与锁查看。
【2025 最新】 MySQL 数据库安装教程（超详细图文版）：从下载到配置一步到位
qq_51572290的博客
11-13 6万+
本文详细介绍MySQL 8.4.7在Windows系统的安装配置流程：首先确认系统环境要求，从官网下载社区版安装包；然后解压文件并配置环境变量，创建my.ini配置文件；接着初始化数据库、注册服务并启动；最后讲解修改root密码、关闭skip-grant-tables安全设置等操作。同时提供可视化工具连接方法，并针对常见安装问题进行排查说明，包括服务启动失败、密码错误等问题解决方案，帮助新手快速完成MySQL环境搭建。
最新MySQL面试题(2025超详细版)
java龙王的博客
01-03 4万+
本内容涵盖了MySQL数据库的基本操作及其应用，涉及SQL执行顺序、查询优化、常用聚合函数、事务及其ACID特性、视图、分页技术、变量和用户定义函数、FULLTEXT搜索、查询缓存等。还介绍了数据库设计与管理中的重要概念，如存储引擎（InnoDB与MyISAM）、外键、逻辑与物理备份、高并发数据修改、重复数据处理、范式设计、分布式数据库及事务管理等。此内容为数据库管理与设计提供了实用的指导，帮助用户理解和应用数据库的关键概念与技术。
【2026最新】 MySQL 数据库安装教程（超详细图文版）：从下载到配置一步到位
最新发布
weixin_51725318的博客
05-20 1449
本文详细讲解了 2025 年最新版 MySQL 的免安装版安装流程，从下载、配置到登录验证，每一步都配有具体操作和说明，同时覆盖了新手常见的问题排查。MySQL 安装的核心是确保配置文件路径正确、以管理员身份操作服务，以及及时修改默认密码保障安全。《网络安全从零到精通全套学习大礼包》
2025最新OpenEuler系统安装MySQL的详细教程
猫头虎技术团队：授渔优于赠鱼，兴趣引领智慧，探索之乐尤显珍贵。商务合作+：Libin9iOak ，万粉变现+：CSDNWF，猫头虎承诺每年免费为100名C站创作者做账号流量诊断服务！全网搜：猫头虎技术团队，点击文章底部名片或直接私信我一切皆可谈，快找虎哥！
06-13 9519
2025最新OpenEuler系统安装MySQL的详细教程 一、环境准备 二、查看官方源中MySQL支持情况 三、安装MySQL 四、初始化密码和安全配置 ✅ 解决方案：**重置 MySQL root 密码（兼容MySQL 8 on openEuler）** ✅ 第一步：停止 mysqld 服务 ✅ 第二步：使用“跳过授权表”模式启动 MySQL（临时无密码） ✅ 第三步：无密码登录 MySQL ✅ 第四步：恢复正常启动服务 重要‼️步骤：设置远程访问权限 ✅ 1. 登录 MySQL（已登录可跳过）
【保姆级图文教程】最新Windows系统QT下载、安装、入门、配置VS Qt环境，图文详细、内容充实
热门推荐
介绍自己在学习过程中遇到的一些疑问及解决方法，个人知识总结的同时做一个知识分享~
09-03 35万+
本文以详细的图文介绍了如何下载安装Qt编程环境，包括Qt Creator和VS环境，以及一些基础的入门知识，图文详细，新手入门这一篇就够了，看完包会！
mysql 查询正在进行的事务
stephen
06-08 1万+
SELECT * FROM information_schema.innodb_trx ；
information_schema(INNODB_TRX | INNODB_LOCKs | INNODB_LOCK_waits)
尼古拉斯.张飞的博客
11-21 2501
这三条语句 分别 查 事务 、锁、锁等待： SELECT * FROM information_schema.INNODB_TRX; -- 命令是用来查看当前运行的所有事务： SELECT * FROM information_schema.INNODB_LOCKs; -- 命令是用来查看当前出现的锁； SELECT * FROM information_schema.INNODB_L...
MySQL学习：事务的隔离
小朱小朱绝不认输的博客
09-01 2975
简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。 一、事务 数据库事务（Database Transaction）： 一组具有ACID属性的SQL命令组成的单个逻辑工作单元，称为数据库事务。 事务是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割
mysql问题分析、解决
有梦想的咸鱼的博客
03-21 1052
查看正在进行中的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 杀死进程id(就是上面命令查到的trx_mysql_thread_id列) kill 线程id 查看当前锁定的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 查看当前等锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 查看超过60s的长事务 s.
mysql 常见报错
songjuntao8的专栏
01-13 424
记一次特别奇怪的mysql报错 1.怀疑是否有正在执行的事务没有提交，进行查询。 select * from information_schema.PROCESSLIST a , information_schema.INNODB_TRX b where b.trx_mysql_thread_id=a.id order by 1 desc 查看数据库的进程表 show full processlist; 查看事物表 select* from information_schema.inn.
【超详细图文教程】2025年最新Win10 系统安装 MySQL 教程
Himan21的博客
09-10 3475
本文是一篇针对Windows 10系统的MySQL 9.4.0安装教程，帮助用户快速完成MySQL的安装与基础配置。
【2025最新】Jira 10.xx在Windows上安装的详细安装教程
小for学习栏
01-20 4829
2025年j最新ira安装较长
【2025最新】Jira、Confluence安装教程（支持最新版）
小for学习栏
01-20 5585
Jira、confluence2025年最新安装教程
MySQL下载安装最新版，2025最新保姆级教程
qq_52291181的博客
07-31 1万+
本文简要介绍了MySQL数据库的下载安装流程及常见问题解决方法。首先需从官网下载安装包，安装过程中可能遇到缺少Visual Studio组件的错误，需先下载相应插件。安装步骤包括选择安装路径、配置root密码等设置，最后执行安装。验证安装是否成功可通过cmd进入bin目录执行mysql命令，使用status查看版本信息。特别提醒安装路径不要包含中文或特殊字符。全文提供了详细的图文指引链接，帮助用户顺利完成MySQL的安装配置。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值