7.MySQL语法

By youfang at: 2022-08-13

MySQL语法

Mysql 修改auto_increment的值

1
2
3
alter table b_storage_bin auto_increment = 1000;

select auto_increment from information_schema.tables where table_schema='spd001' and table_name='b_storage_bin';

去重取ID

1
2
3
4
--去重取ID
SELECT * FROM change_face_used_picture cfup
INNER JOIN (SELECT MAX(id) as mid FROM change_face_used_picture WHERE user_id = 15 and `status` = 2 GROUP BY url ) t on cfup.id = t.mid
LIMIT 5

update关联查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 示例
UPDATE table_1 t1 left join table_2 t2 on t2.id = t1.tid
SET t1.username = t2.uname
where t1.id > 5;
-- 先查询
SELECT t1.robot_code, LEFT(t1.model_code,15), t2.robot_code FROM `prompter-manager`.prompter_video_train_issue t1
  LEFT JOIN (SELECT r.robot_code, s.scene_code FROM digital.scene s
            INNER JOIN digital.robot r on s.robot_id = r.id) t2 
  on LEFT( t1.model_code,15) = t2.scene_code
	WHERE t1.robot_code is null and t1.model_code != '' and t1.model_code != '233289519239237';
-- 后更新
update `prompter-manager`.prompter_video_train_issue t1
  LEFT JOIN (SELECT r.robot_code, s.scene_code FROM digital.scene s
            INNER JOIN digital.robot r on s.robot_id = r.id) t2 
  on LEFT(t1.model_code,15) = t2.scene_code
set t1.robot_code = t2.robot_code
	WHERE  t1.robot_code is null and t1.model_code != '' and t1.model_code != '233289519239237';

随机数函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql产生随机数小结一下，可以为mysql的表生成大量的随机数：
1） 产生0到10000间的随机数
   SELECT RAND() * 10000;
对应产生相应的整数
   SELECT FLOOR(RAND() * 10000)
2） 使用md5()产生32位随机字符串
SELECT MD5(RAND() * 10000)
3） 产生500-1000间的整数
  SELECT FLOOR( 500 + RAND() * (1000 - 500))
  
  
### 20220801假数据
ALTER TABLE `drama`.`t_m_album`
    ADD COLUMN `popularity_count` int NULL DEFAULT 0 COMMENT '人气假数据' AFTER `update_time`,
ADD COLUMN `sales_count` int NULL COMMENT '销量假数据' AFTER `popularity_count`;
ALTER TABLE `drama`.`t_m_drama`
    ADD COLUMN `popularity_count` int NULL DEFAULT 0 COMMENT '人气假数据' AFTER `update_time`,
ADD COLUMN `sales_count` int NULL COMMENT '销量假数据' AFTER `popularity_count`;

UPDATE `drama`.`t_m_album` set popularity_count = 3100,sales_count=4100;
UPDATE `drama`.`t_m_drama` set popularity_count = 3100,sales_count=4100;
-- 随机5000~10000的数
-- UPDATE `drama`.`t_m_album` set popularity_count = FLOOR( 5000 + RAND() * (10000 - 5000)),sales_count=FLOOR( 5000 + RAND() * (10000 - 5000));
-- UPDATE `drama`.`t_m_drama` set popularity_count = FLOOR( 5000 + RAND() * (10000 - 5000)),sales_count=FLOOR( 5000 + RAND() * (10000 - 5000));

mysql 获取随机10条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT * FROM s_user WHERE id>= ((SELECT MAX(id) FROM s_user)-(SELECT MIN(id) FROM s_user)) * RAND() + (SELECT MIN(id) FROM s_user)  LIMIT 10

SELECT * FROM user_video_robot WHERE id>=((
  SELECT MAX(id) FROM user_video_robot where create_time > '2024-03-25'
)-(
  SELECT MIN(id) FROM user_video_robot where create_time > '2024-03-25'
)) * RAND() + (
 SELECT MIN(id) FROM user_video_robot where create_time > '2024-03-25'
)  LIMIT 10




select * from user_video_robot uvr  where id in(

    select k.id from (

         SELECT FLOOR( 7477 + RAND() * (7754 - 7477)) as id
         union
         SELECT FLOOR( 7477 + RAND() * (7754 - 7477)) as id

     ) k
)

MySQL计算两日期/时间之间相差的秒数

1
2
3
4
5
SELECT TIMESTAMPDIFF(SECOND,'2020-03-27 10:38:00','2020-03-27 10:38:31') FROM DUAL;

31

-- FRAC_SECOND 表示间隔是毫秒，SECOND 秒，MINUTE 分钟，HOUR 小时，DAY 天，WEEK 星期，MONTH 月，QUARTER 季度，YEAR 年；

新增与更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 新增操作，当有重复键时忽略本次操作！
insert ignore INTO `t_wx_group` (`id`, `wx_group_id`, `wx_group_name`) VALUES ( id, '2', '大盘4421群');
-- 更新操作：会刷新主键！！会先删除原有的数据记录，然后执行插入新的数据。
replace INTO `t_wx_group` (`id`, `wx_group_id`, `wx_group_name`) VALUES ( id, '2', '大盘4421群');
-- 新增操作，当遇到唯一索引的重键后执行更新操作
insert into`t_wx_group` (`wx_group_id`, `wx_group_name`) values ( '4', '222')
on duplicate key update  wx_group_name='222';
-- 新增操作，当遇到唯一索引的重键后执行更新操作 2
INSERT INTO student (name, age)
SELECT * from (SELECT GROUP_CONCAT(name) as n, age FROM student_1 GROUP BY age) as t
ON DUPLICATE KEY UPDATE name = t.n;
-- 根据查询结果插入
INSERT INTO `user` (`user_id`, `corp_id`, `phone`, `user_name`, `user_sex`, `white_list`, `create_time`, `update_time`, `del_flag`)
SELECT t1.id, t1.corp_id, t1.mobile,t1.username, 1,0,t1.create_time,IFNULL(t1.update_time,t1.create_time), 0 FROM scrm.sys_user t1
  LEFT JOIN `user` t2 on t1.id = t2.user_id
WHERE t1.del_flag = 0 and t2.user_id is null and t1.mobile is not null;
insert select where
1
2
3
4
5
6
-- 1
INSERT INTO t_dict  ( name, code, parent_id,   status )  SELECT  'ZZZ', 'ZZZ', 0,   1  FROM DUAL  WHERE NOT EXISTS (select id from t_dict where id = 21560)
-- 2
INSERT INTO tts_label_model ( model_id,label_id, order_no, create_time, update_time, is_deleted)
select tm.id ,(select tlo.id from tts_label_order tlo where tlo.label_type = 'df_category' and tm.user_category = tlo.label_name) as label_id  , 0, now(), now(),0 from tts_model tm
where tm.id >= 500331;

重新修改ID

UPDATE scene SET id = REPLACE(id, ‘0000’,’’) WHERE id > 95800336;

重新设置递增值

ALTER TABLE scene AUTO_INCREMENT= 195800330;

DATE_ADD() 函数向日期添加指定的时间间隔。

DATE_ADD(date,INTERVAL expr type)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
1
2
3
4
5
6
7
8
9
10
11

SELECT
    (case
        when (t1.delivery_time is null and now() > DATE_ADD(t1.create_time,INTERVAL 48 HOUR)) then '已逾期'
        when (t1.delivery_time is not null and t1.delivery_time < t1.create_time) then '-'
        when (t1.delivery_time is not null and t1.delivery_time < DATE_ADD(t1.create_time,INTERVAL 48 HOUR)) then '未逾期'
        when (t1.delivery_time is not null and t1.delivery_time > DATE_ADD(t1.create_time,INTERVAL 48 HOUR)) then '逾期交付'
        else '制作中'
    end
        ) as overdueStatus
FROM table_name_1 t1

CASE WHEN 操作

1
2
3
4
5
6
7
8
9
10
11

SELECT
    (case
        when (t1.delivery_time is null and now() > DATE_ADD(t1.create_time,INTERVAL 48 HOUR)) then '已逾期'
        when (t1.delivery_time is not null and t1.delivery_time < t1.create_time) then '-'
        when (t1.delivery_time is not null and t1.delivery_time < DATE_ADD(t1.create_time,INTERVAL 48 HOUR)) then '未逾期'
        when (t1.delivery_time is not null and t1.delivery_time > DATE_ADD(t1.create_time,INTERVAL 48 HOUR)) then '逾期交付'
        else '制作中'
    end
        ) as overdueStatus
FROM table_name_1 t1

GROUP_CONCAT

1
2
3
4
5
6
7
8
9
#### 在一行列出所有列：
select GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ') from INFORMATION_SCHEMA.Columns where table_name='tb_org' and table_schema='f6dms_20160522';

拼接到一行：
SELECT GROUP_CONCAT(name SEPARATOR ', ') from city

#### 合并 mysql 一列多行 转换成一行
SELECT GROUP_CONCAT(tt.member_id SEPARATOR ', ') from t_member_info tt WHERE nickname is not null
SELECT GROUP_CONCAT(tt.member_id SEPARATOR ','), '1' as name FROM t_member_info tt WHERE nickname is not null GROUP BY name

DATE_FORMAT函数

1
2
3
4
5
6
7
8
9
10
11
12
13
DATE_FORMAT(now(), '%Y-%m-%d 00:00:00')



select DATE_FORMAT( DATE_ADD(NOW(), INTERVAL 1 MINUTE),'%Y-%m-%d %H:%i:%s');
select DATE_FORMAT( DATE_ADD(NOW(), INTERVAL 1 MINUTE),'%Y-%m-%d %H:%i:00');


<if test="startDateTime != null">and rsr.create_time >= #{startDateTime}</if>
<if test="endDateTime != null"><![CDATA[and DATE_FORMAT(rsr.create_time,'%Y-%m-%d') <= #{endDateTime}]]></if>



Mysql中json类型查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 提取JSON字段的值
SELECT tts_extend_json->'$.popularity' FROM tts_model;
-- 去除双引号
-- write_param ->>'$.videoUrl' as '原视频',
-- JSON_UNQUOTE(write_param ->'$.videoUrl') as '原视频',
-- TRIM(BOTH '"' FROM JSON_EXTRACT(write_param, '$.videoUrl')) as '原视频',
-- TRIM(BOTH '"' FROM write_param ->'$.videoUrl') as '原视频',

-- 过滤JSON数组：
SELECT * FROM tts_model WHERE JSON_CONTAINS(tts_extend_json->'$.popularity', '199');

-- 查询JSON字段的键名
SELECT JSON_KEYS(tts_extend_json) FROM tts_model;


## 重新设置值 NULL时不行
update student set ext = json_set(ext, '$.aaa', 'XS111') where id = 2;

## NULL的时候赋值
update student set ext = json_object('aaa','XS111') where id = 2;

UNION & UNION ALL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

--  UNION ALL和UNION的差别就在ALL上面，第一个叫联合所有，说明会显示前后两个查询所有的数据，而UNION没有ALL(所有)这个单词，实现将前后两个查询的数据联合到一起后，去掉重复的数据显示。

select * from (

select if(task_type = 10,'有口型','无口型') as '类型' , write_param ->>'$.videoUrl' as '原视频', concat('https://gy.cdn.guiji.cn', synthesis_url)   as '合成视频',create_time  as '创建时间' from `ffo-toc`.ffo_work fw where create_time > '2024-03-11' and create_time < '2024-03-18' and synthesis_status = 2 and task_type in(10,11)

union all

select '数字人克隆' as '类型', uvr.video_url as '原视频', concat('https://gy.cdn.guiji.cn', fw.synthesis_url)  as '合成视频',fw.create_time  as '创建时间'
from `ffo-toc`.ffo_work fw
left join `ffo-toc`.user_video_robot uvr on write_param ->'$.robotId' = uvr.id
where fw.create_time > '2024-03-11' and fw.create_time < '2024-03-18' and fw.synthesis_status = 2 and fw.task_type in(12)

) t order by t.创建时间



select if(task_type = 10,'视频翻译-有口型','视频翻译-无口型') as '类型' , 
-- write_param ->'$.videoUrl' as '原视频', 
-- JSON_EXTRACT(write_param, '$.videoUrl') as '原视频',

-- 去除双引号
        write_param ->>'$.videoUrl' as '原视频',
-- JSON_UNQUOTE(write_param ->'$.videoUrl') as '原视频',
-- TRIM(BOTH '"' FROM JSON_EXTRACT(write_param, '$.videoUrl')) as '原视频',
-- TRIM(BOTH '"' FROM write_param ->'$.videoUrl') as '原视频',

    concat('https://gy.cdn.guiji.cn', synthesis_url)   as '合成视频',create_time  as '创建时间' from `ffo-toc`.ffo_work fw where create_time > '2024-03-11' and create_time < '2024-03-18' and synthesis_status = 2 and task_type in(10,11)


Text类型的长度

Q：varchar 到底能存多少个字符？
按照官网说法最大值是 65535bytes，utf8mb4 编码情况下每个字符占 4 个 bytes，最大值应该为 16383.75
这与表使用的字符集相关，latin1、gbk、utf8、utf8mb4 编码存放一个字符分别需要占 1、2、3、4 个字节

2.
mysql中text 最大长度为65,535(2的16次方–1)字符的TEXT列。 //报错： 72896

如果觉得text长度不够，可以选择：
1、MEDIUMTEXT最大长度为16,777,215
2、LONGTEXT最大长度为4,294,967,295

当查询不存在时添加记录

1
2
3
INSERT INTO scorpio.student ( name, age, birth, ext) 
SELECT '张辽5', 2, '2022-05-31 09:13:28', NULL FROM DUAL
WHERE NOT EXISTS ( SELECT id FROM scorpio.student WHERE name = '张辽' )

MYSQL数据库锁解决ERROR:Lock wait timeout exceeded; try restarting transaction

使用InnoDB表类型的时候，锁等待超过了innodb_lock_wait_timeout（默认是50s）设置的时间,所以报错

kill 线程ID(trx_mysql_thread_id)

1
2
3
4
5
6
SELECT * FROM information_schema.INNODB_TRX; 
-- kill trx_mysql_thread_id
kill 2977;
kill 2487;
kill 1254;

Mysql行转列

https://blog.csdn.net/qq_58148854/article/details/135978755

1
2
3
4
5
6
7
8
9
10
11
select ot.project_type,

SUM(if(ot.point_no = 'A0001', cnt, 0)) as 'A0001',
SUM(if(ot.point_no = 'A0002', cnt, 0)) as 'A0002'
from (

select project_type , point_no , 1  as cnt
from data_point  

) ot group by ot.project_type

查询表中的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 查询表中的字段
select
	COLUMN_NAME
from
	INFORMATION_SCHEMA.COLUMNS
where
	TABLE_SCHEMA = 'scorpio'
	and TABLE_NAME = 'student';
	

-- 查询表中的字段
SHOW COLUMNS FROM student ;-- LIKE 'name';


mysql查询序号递增

1
2
3
4
5
6
7

SET @row_number = 0;
SELECT
  (@row_number:=@row_number + 1) AS num,
  t.*
FROM
  (SELECT * FROM your_table) AS t;

笛卡尔积

笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行

发生原因：没有有效的连接条件

1
2
3
4
5
6
7
8
9
10
11
12

-- SQL
SELECT * FROM table1, table2;

-- 利用笛卡尔积插入数据

INSERT INTO `user_video_classify_relation` (`classify_id`, `bind_id`)

SELECT * from
             ( SELECT id as classify_id FROM `user_video_classify` WHERE name = '广告宣传' and type = 2) t1
        ,
             ( SELECT id as bind_id FROM `user_video_tts` WHERE speaker = '阿若') t2;

查询重复数据、删除重复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 重复数据

SELECT age, count(age) FROM student  GROUP BY age HAVING COUNT(age) > 1;

-- 保留ID小的数据，删除age相同的数据
DELETE t1 FROM student t1
INNER JOIN student t2 
WHERE t1.id > t2.id AND t1.age = t2.age;

-- 保留ID大的数据，删除age相同的数据
DELETE t1 FROM student t1
INNER JOIN student t2 
WHERE t1.id < t2.id AND t1.age = t2.age;

查询统计

SELECT r.id, r.robot_name, IFNULL(b.status, 0) as ‘是否喜欢’,
(select count() from user_video_behavior v where v.related_id = r.id and v.type = 1 and v.status = 1) as ‘likeCount’,
(select count() from user_video_behavior v where v.related_id = r.id and v.type = 2 and v.status = 1) as ‘shareCount’
from user_video_robot r
left join user_video_behavior b on r.id = b.related_id and b.type = 1 and b.user_id = 200
WHERE r.type = 2;

查询统计（有条件）

SELECT SUM(er.employ_duration) as ‘totalEmployDuration’, COUNT(*) as ‘totalEmployCount’, SUM(er.income) as ‘totalIncome’
, SUM(IF(er.status = 1,er.income,0)) as ‘canWithdrawalPrice’
, SUM(IF(er.status = 2,er.income,0)) as ‘auditWithdrawalPrice’
, SUM(IF(er.status = 3,er.income,0)) as ‘withdrawalPrice’
FROM user_video_robot r
LEFT JOIN user_video_employ_record er on r.id = er.robot_id
WHERE r.free_type = 5 and r.user_id = 100

MySQL中插入数据时使用条件NOT EXISTS

INSERT INTO user_video_behavior (related_id, type, user_id)
SELECT 1, 1, 4 from dual WHERE not EXISTS (
SELECT 1 FROM user_video_behavior where related_id = 1 and type = 1 and user_id = 4
);

在 主键 或者 唯一约束 重复时，执行更新操作。

INSERT INTO user_video_behavior (id, related_id, type, user_id) values (1000, 1, 1, 4)
ON DUPLICATE KEY UPDATE status = 2;

在 主键 或者 唯一约束 重复时，先 delete 再 insert。

REPLACE INTO user_video_behavior (id, related_id, type, user_id) values (1000, 1, 1, 4)

MyBatis动态SQL示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<select id="queryTtsList" resultType="ai.guiji.face.dto.UserVideoTtsVo">
    select * from (select t1.*, (case
    when (ifnull(t2.train_status, 20) = 20 and ifnull(t1.train_status, 20) = 20 and ifnull(t2.demo_video_make_status, 20) = 20 ) then '20'
    when (ifnull(t2.train_status, 20) = 30 or ifnull(t1.train_status, 20) = 30 or ifnull(t2.demo_video_make_status, 20) = 30 ) then '30'
    else '10'
    end
    ) as show_status from user_video_tts  t1
    left join user_video_robot t2 on t1.robot_id = t2.id ) ot
    <where>
        del_flag = 0
        <if test="id != null">and ot.id = #{id}</if>
        <if test="userId != null">and ot.user_id = #{userId}</if>
        <if test="type != null">and ot.type = #{type}</if>
        <if test="lang != null and lang != ''">and (ot.lang is null or ot.lang = '' or ot.lang like concat('%',#{lang},'%'))</if>
        <if test="lang != null and lang == ''">and (ot.lang is null or ot.lang = '')</if>
        <if test="showStatusList != null and showStatusList.size > 0">
            and ot.show_status in
            <foreach collection="showStatusList" open="(" close=")" separator="," item="status">
                #{status}
            </foreach>
        </if>
        <if test="speaker != null and speaker != ''">and ot.speaker = #{speaker}</if>
    </where>
    order by ot.create_time desc
</select>