mysql常用语句

[AlfieriChou]

1. 更新A字段根据B字段*1000

UPDATE `test` SET `create_time` = CONCAT( `time`, '000')

[AlfieriChou]

2. 时间戳转日期

   select 
   `date`,
   from_unixtime(`date` / 1000, '%Y-%c-%d') as `date_str`
   from
   `test`
   where
   `date` = 1652025600000

[AlfieriChou]

3. 获取JSON字段存储中，某个key的value

select
    JSON_EXTRACT(extra, '$.date')
from
    `test`

参考文章

[AlfieriChou]

4. 去除某个字符串字段的双引号

UPDATE `test` SET `address` = TRIM(BOTH '"' FROM `address`) 

[AlfieriChou]

5. 从某个JSON类型字段，获取某个key的value，并且填充到一个额外的字段中

• 数字

update
    `test`
set
    `amount` = JSON_EXTRACT(extra, '$. amount')
where
    `id` = 1

• 字符串

update
    `test`
set
    `address` = TRIM(BOTH '"' FROM JSON_EXTRACT(extra, '$. address'))
where
    `id` = 1

参考文章

[AlfieriChou]

6. 单字段且查询

select
  `account_id`
from 
  `account_tag`
where
  `tag_id` in (33, 82)
group by
  `account_id`
having
  count(distinct `tag_id`) = 2

[AlfieriChou]

7. 时间对比

select
  id,
  created_at_str,
  date_str,
  datediff(created_at_str, date_str) as diff_day
from
  (
    SELECT
      id,
      FROM_UNIXTIME(
        t_table.created_at / 1000
      ) as created_at_str,
      FROM_UNIXTIME(
        t_table.date / 1000 + 86400 * 3
      ) as date_str
    FROM
      t_table
    WHERE
      id = '11111111111'
  ) as temp