mysql常用json查询
格式化数据格式
-- 格式化DECIMAL
select
CONVERT(
total_money,
DECIMAL(15, 2)
) as convert_money
from
`agent_data`
convert_money|
-------------+
8840.00|
0.00|
-- 格式化时间
select
CONVERT(created_at, DATE) as created_at,
CONVERT(created_at, DATETIME) as created_at2
from
`agent_data`
created_at|created_at2 |
----------+-------------------+
2022-05-27|2022-05-27 15:22:59|
2022-05-27|2022-05-27 15:31:10|
保留小数位数,多的截取
select
TRUNCATE(total_money, 2) as truncate_money
from
`agent_data`
truncate_money|
--------------+
8840|
IFNULL 判断
-- 在SUM等计算的时候,如果任何参数为null,那么计算的结果也将会是null,是不对的,需要手动判断
select
SUM(IFNULL(total_money, 0)) as sum_total_money
from
`agent_data`
sum_total_money|
---------------+
10491|
IF 判断
-- 如果type==1或者==2统计total_money字段否则统计money字段
select
SUM(
IF(
`type` = 1
OR `type` = 2,
`total_money`,
`money`
)
) as `sum_total_money`
from
`agent_data`
sum_total_money|
---------------+
9715|
逗号分割的文本数据进行查询
-- FIND_IN_SET(needle,haystack);
-- 第一个参数needle是要查找的字符串。第二个参数haystack是要搜索的逗号分隔的字符串列表。
select
`remark_name`,
FIND_IN_SET("员工备注2", `remark_name`) as find_num
from
`agent_data`
WHERE
FIND_IN_SET("员工备注2", `remark_name`) > 0
remark_name|find_num|
-----------+--------+
员工备注2 | 1|
json格式读取
-- 索引json数据值查询
select
rule_key,
JSON_EXTRACT(rule_key, '$[0]') as json_data_1
from
`agent_data`
WHERE
JSON_EXTRACT(rule_key, '$[0]') = 'force_popup'
rule_key |json_data_1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
["force_popup"] |"force_popup"|
["force_popup"] |"force_popup"|
["force_popup", "fixed_profit", "operating_amortization", "view_orders"] |"force_popup"|
["force_popup", "view_order", "view_order_mobile_number_display"] |"force_popup"|
-- 键值对json数据值查询
select
`compute_arr`,
json_extract(`compute_arr`, '$."first"') as json_first,
json_extract(`compute_arr`, '$."first"."3"') as json_first_3
from
`order`
WHERE
json_extract(`compute_arr`, '$."first"') IS NOT NULL
compute_arr |json_first |json_first_3|
-----------------------------------------------------------------------------------------------------+----------------------------------------------------+------------+
{"first": {"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}, "second": {"3": 1, "80": 0, "81": 0}}|{"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}|-480 |
{"first": {"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}, "second": {"3": 1, "80": 0, "81": 0}}|{"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}|-480 |
-- 索引包含键值对json数据值查询
select
`data`,
json_extract(`data`, '$[*]."field"') as `field_arr`
from
`agent_log`
WHERE
id = 106
data |field_arr |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+
[{"field": "equipment_amortization_all_money", "title": "金额", "value": "2.00", "oldvalue": "1.00"}, {"field": "equipment_amortization_day_money", "title": "设备每天的金额", "value": "3.00", "oldvalue": "2.00"}]|["equipment_amortization_all_money", "equipment_amortization_day_money"]|
json匹配
-- JSON_CONTAINS() 函数
-- target_json必需的。一个 JSON 文档。candidate_json 必需的。被包含的 JSON 文档。 path 可选的。一个路径表达式。
-- 匹配json,索引类型: 直接匹配对应的value即可
select
rule_key,
JSON_CONTAINS(
rule_key,
'["fixed_profit", "operating_amortization"]'
) as check_num,
JSON_CONTAINS(
rule_key,
'["fixed_profit"]'
) as check_num2
from
`agent_data`
WHERE
id = 39
rule_key |check_num|check_num2|
------------------------------------------------------------------------+---------+----------+
["force_popup", "fixed_profit", "operating_amortization", "view_orders"]| 1| 1|
-- 匹配json,键值对类型: 对 compute_arr.first.3 的值进行匹配, 注意-480的数值类型需要一致
select
`compute_arr`,
`compute_arr` -> '$."first"."3"',
JSON_CONTAINS(
`compute_arr` -> '$."first"."3"',
'-480'
) as check_num
from
`order`
compute_arr |`compute_arr` -> '$."first"."3"'|check_num|
-----------------------------------------------------------------------------------------------------+--------------------------------+---------+
{"first": {"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}, "second": {"3": 1, "80": 0, "81": 0}}|-480 | 1|
{"first": {"3": -480, "80": 70, "81": 30, "92": 165, "93": 88}, "second": {"3": 1, "80": 0, "81": 0}}|-480 | 1|
-- 匹配json,索引包含键值对类型: 索引包含键值对json数据值查询
select
`data`,
JSON_CONTAINS(
`data` -> "$[*].field",
'["equipment_amortization_all_money"]'
) as check_num
from
`agent_log`
WHERE
id = 106
data |check_num|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+
[{"field": "equipment_amortization_all_money", "title": "设备金额", "value": "2.00", "oldvalue": "1.00"}, {"field": "equipment_amortization_day_money", "title": "设备每天金额", "value": "3.00", "oldvalue": "2.00"}]| 1|
分组查询之后获取对应分组的字段的所有值
select `key`, `type` from `rule`;
key |type |
--------------------------------+-----------------+
force_popup |agent |
no_withdrawal_privileges |agent |
order_details |agent |
lending |agent |
no_withdrawal_privileges |employee |
force_popup |employee |
lend_charging_privileges |employee |
view_order |employee |
select `key`, `type`,GROUP_CONCAT(`key`) from `rule` GROUP BY `type`;
key |type |type_all_key
------------------------+-----------------+----------------------------------------------
force_popup |agent |force_popup,no_withdrawal_privileges,order_det
no_withdrawal_privileges|channeler |no_withdrawal_privileges,order_info
order_info |customer_servicer|order_info
no_withdrawal_privileges|employee |no_withdrawal_privileges,force_popup,lend_char
order_info |logisticser |order_info
no_withdrawal_privileges|outlets |no_withdrawal_privileges,no_login,order_info
json 数组操作新增和删除
/*
原数据:
["tixian_weixin", "dingdan_mx", "tixian_zfb"]
新增: 查询第一个匹配的字符串, 为null就新增
*/
UPDATE
outlets
SET
rule_key = IF(JSON_SEARCH(IFNULL(rule_key, '[]'),
'one',
'tixian_zfb') IS NULL,
JSON_MERGE_PRESERVE(IFNULL(rule_key, '[]'),
'["tixian_zfb"]'),
rule_key)
where
id = 1
/* 删除 */
UPDATE
outlets
SET
rule_key = IF(JSON_SEARCH(IFNULL(rule_key, '[]'),
'one',
'tixian_zfb') IS NULL,
rule_key,
JSON_REMOVE(rule_key,
JSON_UNQUOTE(JSON_SEARCH(IFNULL(rule_key, '[]'),
'one',
'tixian_zfb'))))
where
id = 1
-- 前7天的数据
created_at >= CURDATE() - INTERVAL 7 DAY
-- 计算当前时间和receiving_tim+48小时的差值(秒)
TIMESTAMPDIFF(SECOND, NOW(), receiving_time + interval 48 hour)
-- 60 后参-前参数>0就是正数
SELECT TIMESTAMPDIFF(SECOND, '2023-01-01 12:00:00', '2023-01-01 12:01:00');
mysql一张表同时select和update，报错：You can‘t specify target table ‘xxx‘ for update in FROM clause
-- 把in的数据进行表缓存, 从缓存表里面拿数据id
-- ! 更新数据作为条件时, 需要使用缓存数据: 就是在条件里面把数据查出来
-- You can't specify target table 'work_order' for update in FROM clause
update
store_process
set
local_store_id = 5
where
id in (
SELECT temp.id FROM
(
select
store_process.id
from
store_process
left join order_third_info on order_third_info.biz_order_id = store_process.order_third_no
where
order_third_info.identify_store_id > 0
and order_third_info.identify_store_id = 10502
and order_third_info.biz_order_id = '4131888375398986742'
) temp
)
常见操作
-- 备份表数据(主键一致)
CREATE TABLE migrations_bak AS
SELECT
*
FROM
migrations
WHERE
batch = 2
-- 把一个表数据插入到另一个表(主键一致)
INSERT
INTO
migrations
SELECT
*
FROM
migrations_bak
WHERE
batch = 2
-- 只插入非主键数据,指定字段(字段对应, 主键自增)
INSERT
INTO
migrations (migration,
batch)
SELECT
`migration`,
`batch`
FROM
migrations_bak
WHERE
batch = 2
-- 关联表更新(使用INNER,只有两边都有数据才更新)
UPDATE
migrations
INNER JOIN migrations_bak ON
migrations.id = migrations_bak.id
SET
migrations.batch2 = migrations_bak.batch
where
migrations.id > 0
最简单的去重,不用
distinct而用min/max
select
*,min(order_third_no)
from
wine_data.store_process
where
order_third_no = 'MN63772110651040152'
group by order_third_no
查询不在某些数据的数据
-- 查询1,2,99993000不在wine_identify_code.id的数据
SELECT A.*, B.id FROM (
SELECT '1' AS in_id UNION ALL
SELECT '2' UNION ALL
SELECT '99993000'
) AS A left join wine_identify_code B on A.in_id = B.id
where B.id is NULL
in_id |id|
--------+--+
99993000| |
统计json字段的数据的和
select
SUM(JSON_LENGTH(img_list)) as total_items
from
store_device_img
使用GROUP_CONCAT之前设置最大字节显示, 默认是1024字节完全不够用
Db::select("SET SESSION group_concat_max_len = 1024 * 100");
// 再使用
Db::select("select
MIN(id), GROUP_CONCAT(id)
from
store_process");
$count_arr2 = WineIdentify::selectRaw("
DATE(created_at) AS date,
COUNT(CASE WHEN appraisal_results = 1 THEN 1 ELSE NULL END) AS approved_count,
COUNT(CASE WHEN appraisal_results = 2 THEN 1 ELSE NULL END) AS rejected_count,
COUNT(CASE WHEN appraisal_results = 0 THEN 1 ELSE NULL END) AS pending_count,
SUM(CASE WHEN (balance_money > 0) THEN balance_money ELSE 0 END) AS '预存金额'
")
->whereRaw("created_at >= CURDATE() - INTERVAL $day_number DAY")
->groupBy(Db::raw('DATE(created_at)'))
->orderBy('created_at')
->get()->toArray();
使用
Eloquent ORM in Laravel技巧
// ! 派生表查询(sql子表)
# toRawSql()获取已经原生sql
$sub = DB::table('merchant')
->selectRaw("
id,
DATE_FORMAT(created_at, '%Y-%m-%d') as date_new
")
->groupBy('date_new');
DB::table(DB::raw("({$sub->toSql()}) as _sub"))
->where('date_new', '=', "2025-07-29")
->select()
->get();
// ->leftJoin(DB::raw("({$use_money_for_uid->toRawSql()}) as _sub"), '_sub.merchant_id', '=', 'merchant.id');
// 实际生成sql
select
*
from
(
select
id,
DATE_FORMAT(created_at, '%Y-%m-%d') as date_new
from
`merchant`
group by
`date_new`
) as _sub
where
`date_new` = '2025-07-18'
// 实际sql
select
merchant.id
from
`merchant`
left join `product_account` as `product_account` on
`merchant`.`id` = `product_account`.`merchant_id`
left join `product_account_consume` as `product_account_consume` on
`product_account`.`id` = `product_account_consume`.`account_id`
left join (
select
merchant_id,
SUM(use_money) as use_money
from
`product_account`
where
`merchant_id` > 0
group by
`merchant_id`) as _sub on
`_sub`.`merchant_id` = `merchant`.`id`
where
`product_account_consume`.`date` between '2025-08-12 00:00:00' and '2025-08-12 23:59:59'
and `merchant`.`id` > '0'
group by
`merchant`.`id`
// ! 典型示例: 查询累计明细超过100和100以下的用户的数量
$sub = DB::table('product_account_consume')
->selectRaw("
account_id,
SUM(spend) AS sum_spend
")
->groupBy('account_id');
DB::table(DB::raw("({$sub->toSql()}) as _sub"))
->selectRaw("
CASE
WHEN (sum_spend < 100) THEN '金额100以下'
WHEN (sum_spend >= 100) THEN '金额100以上'
ELSE '其他'
END AS layer,
count(account_id) AS count_num
")
->groupBy('layer')
->get();
对查询的值别名
select
supplier.email as "邮箱",
CASE
WHEN (supplier_money_log.change_type = "yusou") THEN '预收'
WHEN (supplier_money_log.change_type = "order_lease") THEN '租赁订单消费'
ELSE '其他'
END AS "类型",
supplier_money_log.remark as "备注"
多个表全连接
select
id
from
merchant
union all
select
id
from
supplier
时间字段比较, 当字段的值为null时会无法比较, 结果都是false
SELECT
`order`.over_at,
`order`.no
FROM
`order`
WHERE
`order`.over_at is null
over_at|no |
-------+-------------------------+
|ZL-DD17555670189224000595|
|ZL-DD17555681927177000349|
|ZL-DD17555685535208000671|
|CZ-DD17555687178142 |
|CZ-DD17555689176141 |
|CZ-DD17555836631024 |
-- 增加时间比较, 数据就会查不出来
SELECT
`order`.over_at,
`order`.no
FROM
`order`
WHERE
`order`.over_at is null
and NOW() > over_at
计算日期时间的时间戳秒间隔
select
`order`.pay_at,
`order`.created_at,
UNIX_TIMESTAMP(pay_at) - UNIX_TIMESTAMP(created_at) as diff_seconds
from
`order`
where
`order`.pay_status = 20
pay_at |created_at |diff_seconds|
-------------------+-------------------+------------+
2025-07-25 11:22:18|2025-07-25 11:22:12| 6|
如果要分组对某个字段的最大/最小的数据值需要额外进行关联表(关联表取分组后的极限值)
-- 取工单最新的一条操作日志
select
work_order_id,
id,
created_at,
type
from
work_order_log
where
id in (
select
max(id)
from
work_order_log
group by
work_order_id
)
group by
work_order_id
使用已知的数据作为缓存表, 比如日期
$base = convertToUnionAllSql([
['date' => "2025-09-17"],
['date' => "2025-09-16"],
]);
Order::leftJoin(DB::raw("($base) as _temp"), function ($join) {
$join->on('_temp.date', '=', Db::raw("DATE_FORMAT(order.created_at, '%Y-%m-%d')"));
})->count();
Db::table(DB::raw("($base) as _temp"))->leftJoin('order', function ($join) {
$join->on('_temp.date', '=', Db::raw("DATE_FORMAT(order.created_at, '%Y-%m-%d')"));
})->count();
select
count(*) as aggregate
from
`order`
left join (
select
'2025-09-17' as date
union all
select
'2025-09-16') as _temp on
`_temp`.`date` = DATE_FORMAT(order.created_at, '%Y-%m-%d')
//
select
count(*) as aggregate
from
(
select
'2025-09-17' as date
union all
select
'2025-09-16') as _temp
left join `order` on
`_temp`.`date` = DATE_FORMAT(order.created_at, '%Y-%m-%d')
/**
* 把列表的对象转为临时表
*
* @param array $data
[
['d' => 1],
['d' => 2],
]
* @return string
* @author jcleng
*/
function convertToUnionAllSql($data) {
$lines = [];
foreach ($data as $index => $row) {
$values = [];
foreach ($row as $key => $value) {
// 转义单引号并包裹值
$escaped = "'" . str_replace("'", "''", $value) . "'";
// 第一行添加列别名
if ($index === 0) {
$values[] = $escaped . " AS " . $key;
} else {
$values[] = $escaped;
}
}
$lines[] = "SELECT " . implode(", ", $values);
}
return implode("\nUNION ALL\n", $lines);
}
多个字段排序
select
*
from
`order`
order by
case
when order_status = 150 then 0
when order_status = 130 then 1
else 3
end asc,
created_at asc;
-- 根据值排序
order by FIELD(user_lv, 'S', 'A', 'B', 'C', 'D');