MySql 操作记录

1，一些特殊表

查询表的存在

 select * from information_schema.tables where table_schema = 'databaseName' table_name like '%tableName%'

查询字段的存在

 select * from information_schema.columns where table_schema='databaseName' and table_name='tableName' and column_name ='columnName';

2，查询配置

查询支持的数据库引擎

 show engines;

查看 mysql 物理文件位置

 show variables like 'datadir'；

3，一些设置

关闭mysql5.7新特性对衍生表的合并优化

 set session optimizer_switch='derived_merge=off';

4，一些 sql 经验

1. 大数据量表分页优化

用覆盖索引代替全部

 SELECT
	* 
FROM
	pro2 
WHERE
	id >= ( SELECT id FROM pro2 LIMIT 10000000, 1 ) 
	LIMIT 10

2. with 关键字的使用

创建一个临时表很好用

  WITH flow AS (
        SELECT workflow_id requestId, max( create_time ) create_time
        FROM ${table}
        WHERE model_id = #{modelId}
        GROUP BY workflow_id
        ORDER BY max( create_time ) DESC
        limit #{skip}, #{pageNum}
  )
  SELECT
      flow.requestId,
      base.workflowId,
      base.requestName,
      color.`enable`,
      color.id workflowColorId
  FROM
		    flow
		LEFT JOIN workflow_requestbase base ON flow.requestid = base.requestid
		LEFT JOIN ecology_workflow_color color ON flow.requestid = color.request_id and color.model_id = #{modelId} AND color.model_version = #{modelVersion}

3. 唯一索引冲突解决方案

1、insert ignore

 INSERT IGNORE INTO users (name)
VALUES ('John Doe');

使用 INSERT IGNORE 语句后，如果插入的数据与已存在的数据冲突，MySQL 将会忽略这个冲突，并继续执行插入操作。如果插入的数据与已存在的数据不冲突，将会正常插入数据库。

2、replace

replace是insert的增强版
replace into 首先尝试插入数据到表中，

如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
否则，直接插入新数据

 REPLACE INTO ecology_workflow_color ( model_id, model_version, request_id ) 
VALUE(1,2,1)

由于唯一索引冲突的时候，会先删除数据再插入，如果是自增主键就会变，可能导致关联出现问题。

3、INSERT INTO ... ON DUPLICATE KEY UPDATE

当主键重复的时候，会根据 update 后面的语句更新。

 INSERT INTO table_name (column1, column2, ...)
  VALUES (value1, value2, ...)
  ON DUPLICATE KEY UPDATE column1 = values(value1), column2 = value2, ...;

ON DUPLICATE KEY UPDATE column1 = values(value1) 表示更新为 value1 字段的值，
ON DUPLICATE KEY UPDATE column2 = value2 表示更新为固定值 value2

4. 自连接递归

 with recursive temp as (
select * from prj_projectinfo p where  id= 106
union all 
 select t.* from prj_projectinfo t inner join temp t2 on t2.id = t.parentid 
)
select id,name,parentid  from temp

recursive mysql 中的递归

5. 生成大量数据

1. 建表

 create table t(id         bigint not null auto_increment primary key,
     mobile     bigint,
     password   varchar(64),
     username   varchar(64),
     sex        tinyint not null default 1,
     birthday   datetime,
     amount     decimal(18,2), 
     ismaster   bool,
     istest     bit(1), 
     updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

2. 存储过程

 DELIMITER $$
DROP PROCEDURE IF EXISTS proc_gen_user;
CREATE PROCEDURE proc_gen_user(l_cnt BIGINT)
BEGIN
   DECLARE X INT DEFAULT 0;
   DECLARE p CHAR(11);
 
   WHILE X < l_cnt
   DO
      SET X = X + 1;
      SET p =
             CONCAT('1',
                    SUBSTRING(CAST(3 + (RAND() * 10) % 7 AS CHAR(50)), 1, 1),
                    RIGHT(LEFT(TRIM(CAST(RAND() AS CHAR(50))), 11), 9));
      INSERT INTO t(mobile,PASSWORD,username,sex,birthday,amount,ismaster,istest)
         VALUES (
                   p,
                   MD5(CEILING(RAND() * 1000000)),
                   CONCAT(
                     SUBSTRING(REPLACE('赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张
                          孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎
                          鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤
                          滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄
                          和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞
                          熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭
                          梅盛林***钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫
                          经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚
                          程嵇邢滑裴陆荣翁荀羊於惠甄曲家封芮羿储靳汲邴糜松
                          井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫
                          宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄
                          印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴鬱胥能苍双
                          闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍卻璩桑桂濮牛寿通
                          边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容
                          向古易慎戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东
                          欧殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空
                          曾毋沙乜养鞠须丰巢关蒯相查后荆红游竺权逯盖益桓公',' ',''),
                         FLOOR(1 + 400 * RAND()),1),
                      SUBSTRING(REPLACE(
                         '明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中
                          正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜
                          敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山
                          贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传
                          康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵
                          源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和
                          恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连
                          勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁
                          裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤
                          延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝
                          宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦
                          先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦
                          晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅
                          玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',' ',''),
                         FLOOR(1 + 400 * RAND()),1),
                      SUBSTRING(REPLACE(
                         '明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中
                          正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜
                          敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山
                          贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传
                          康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵
                          源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和
                          恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连
                          勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁
                          裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤
                          延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝
                          宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦
                          先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦
                          晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅
                          玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',' ',''),
                         FLOOR(1 + 400 * RAND()),1)),
                   CEILING(RAND() * 10) % 2,
                   DATE(NOW()- INTERVAL (20 + CEILING(RAND() * 100) % 40) YEAR),
                   ROUND(RAND()*100000,2),
                   CEILING(RAND()*10)%2,
                   CEILING(RAND()*10)%2);     
   END WHILE;
END$$
DELIMITER ;

3. 执行

  call proc_gen_user(1000000);

6. 对于 sum 结果为 null

如果sum 没有返回结果的内容，则sum 函数的返回值为 null，不是 0。
sum 求和时会对 null 进行过滤，不计算。
IFNULL函数进行查询，判断第一个参数是否为null，如果是则返回结果为第二个参数(数值自定义)。
IFNULL(sum(), 0)

7. 当联合索引碰上逻辑删除

正常逻辑删除，我们需要将一个字段设置为标记位，比如 is_del = 1。但是有个问题就是，如果删除了一次某个数据，又加了一条同样的数据，此时删除就会发现索引冲突。此时只需要将 is_del 修改成删除时间，具体时间粒度就要看业务精细到什么程度了。

参考：https://www.jianshu.com/p/0a8912a37be0

本文作者：Hi.PrimaryC

本文链接：https://www.cnblogs.com/cnff/p/16785040.html

posted @ 2023-07-06 17:47 primaryC 阅读(38) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

公告

昵称：primaryC 园龄：4年1个月粉丝：2 关注：2

昵称： primaryC
园龄： 4年1个月
粉丝： 2
关注： 2

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

随笔档案

阅读排行榜

评论排行榜

1. RabbitMq 集成到 SpringBoot(2)

Hi.PrimaryC

要有最朴素的生活和最遥远的梦想

MySql 操作记录

1，一些特殊表

2，查询配置

3，一些设置

4，一些 sql 经验

1. 大数据量表分页优化

2. with 关键字的使用

3. 唯一索引冲突解决方案

4. 自连接递归

5. 生成大量数据

6. 对于 sum 结果为 null

7. 当联合索引碰上逻辑删除

公告

搜索

常用链接

随笔分类

随笔档案

相册

阅读排行榜

评论排行榜

推荐排行榜

最新评论

	SELECT
	*
	FROM
	pro2
	WHERE
	id >= ( SELECT id FROM pro2 LIMIT 10000000, 1 )
	LIMIT 10

	WITH flow AS (
	SELECT workflow_id requestId, max( create_time ) create_time
	FROM ${table}
	WHERE model_id = #{modelId}
	GROUP BY workflow_id
	ORDER BY max( create_time ) DESC
	limit #{skip}, #{pageNum}
	)
	SELECT
	flow.requestId,
	base.workflowId,
	base.requestName,
	color.`enable`,
	color.id workflowColorId
	FROM
	flow
	LEFT JOIN workflow_requestbase base ON flow.requestid = base.requestid
	LEFT JOIN ecology_workflow_color color ON flow.requestid = color.request_id and color.model_id = #{modelId} AND color.model_version = #{modelVersion}

	REPLACE INTO ecology_workflow_color ( model_id, model_version, request_id )
	VALUE(1,2,1)

	INSERT INTO table_name (column1, column2, ...)
	VALUES (value1, value2, ...)
	ON DUPLICATE KEY UPDATE column1 = values(value1), column2 = value2, ...;

	with recursive temp as (
	select * from prj_projectinfo p where id= 106
	union all
	select t.* from prj_projectinfo t inner join temp t2 on t2.id = t.parentid
	)
	select id,name,parentid from temp

	create table t(id bigint not null auto_increment primary key,
	mobile bigint,
	password varchar(64),
	username varchar(64),
	sex tinyint not null default 1,
	birthday datetime,
	amount decimal(18,2),
	ismaster bool,
	istest bit(1),
	updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

	DELIMITER $$
	DROP PROCEDURE IF EXISTS proc_gen_user;
	CREATE PROCEDURE proc_gen_user(l_cnt BIGINT)
	BEGIN
	DECLARE X INT DEFAULT 0;
	DECLARE p CHAR(11);

	WHILE X < l_cnt
	DO
	SET X = X + 1;
	SET p =
	CONCAT('1',
	SUBSTRING(CAST(3 + (RAND() * 10) % 7 AS CHAR(50)), 1, 1),
	RIGHT(LEFT(TRIM(CAST(RAND() AS CHAR(50))), 11), 9));
	INSERT INTO t(mobile,PASSWORD,username,sex,birthday,amount,ismaster,istest)
	VALUES (
	p,
	MD5(CEILING(RAND() * 1000000)),
	CONCAT(
	SUBSTRING(REPLACE('赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张
	孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎
	鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤
	滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄
	和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞
	熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭
	梅盛林***钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫
	经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚
	程嵇邢滑裴陆荣翁荀羊於惠甄曲家封芮羿储靳汲邴糜松
	井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫
	宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄
	印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴鬱胥能苍双
	闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍卻璩桑桂濮牛寿通
	边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容
	向古易慎戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东
	欧殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空
	曾毋沙乜养鞠须丰巢关蒯相查后荆红游竺权逯盖益桓公',' ',''),
	FLOOR(1 + 400 * RAND()),1),
	SUBSTRING(REPLACE(
	'明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中
	正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜
	敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山
	贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传
	康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵
	源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和
	恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连
	勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁
	裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤
	延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝
	宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦
	先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦
	晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅
	玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',' ',''),
	FLOOR(1 + 400 * RAND()),1),
	SUBSTRING(REPLACE(
	'明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中
	正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜
	敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山
	贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传
	康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵
	源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和
	恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连
	勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁
	裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤
	延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝
	宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦
	先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦
	晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅
	玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',' ',''),
	FLOOR(1 + 400 * RAND()),1)),
	CEILING(RAND() * 10) % 2,
	DATE(NOW()- INTERVAL (20 + CEILING(RAND() * 100) % 40) YEAR),
	ROUND(RAND()*100000,2),
	CEILING(RAND()*10)%2,
	CEILING(RAND()*10)%2);
	END WHILE;
	END$$
	DELIMITER ;

Hi.PrimaryC

要有最朴素的生活和最遥远的梦想

MySql 操作记录

1， 一些特殊表

2，查询配置

3，一些设置

4，一些 sql 经验

1. 大数据量表分页优化

2. with 关键字的使用

3. 唯一索引冲突解决方案

4. 自连接递归

5. 生成大量数据

6. 对于 sum 结果为 null

7. 当联合索引碰上逻辑删除

公告

搜索

常用链接

随笔分类

随笔档案

相册

阅读排行榜

评论排行榜

推荐排行榜

最新评论

1，一些特殊表