MySQLsum函数求和小数

1、生成随机时间

SELECT DATE_ADD('2021-01-01 00:00:00',  INTERVAL  FLOOR(1 + (RAND() * 10800))   SECOND )

生成随机数

SELECT UUID()

2、group by 优化

• 松散索引
  扫描索引的一部分，需要满足联合索引的左匹配规则。
• 紧凑索引
  范围索引扫描或全扫描。group by中索引有间隙，靠where常量来弥补。
• 理解
  使用时候没必要关心上面哪种，遵循下面规则，效率就可以的。
• 条件

• group by后的字段要按索引顺序排列，且查询字段是group by后的字段的子集。
• 如果select列中包含不在group by中的索引字段，该字段要使用聚合函数
• 执行计划中Extra为：Using index for group-by，Using index，Using index Using where

假如有表t1(c1,c2, c3,c4), 索引为index(c1,c2, c3)

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

--where字段可以是联合索引中任一个
SELECT c1, c2 FROM t1 WHERE c1 < 1 GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > 1 GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < 1 GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = 1 GROUP BY c1, c2;

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

--索引有间隙，填空隙走紧凑索引
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

3、order by优化

• 查询列是order by字段（联合索引）的子集

select c1,c2 from t1 ORDER BY c1,c2；--Using index

• order by 字段非左前缀，where语句为常量，弥补索引缺失
  原因：c1为常量，查询出的c2都是有序的。若c1查询范围很小，代价低于全扫描。可以使用索引排序

select c1,c2 from t1 where c1=1 ORDER BY c2;--Using where; Using index
select * from t1 where c1=1 ORDER BY c2;--是唯一使用select *可排序的场景

• 用不到索引

select * from t1 ORDER BY c1,c2；--Using filesort,非子集会回表，代价高，放弃索引
select c1,c2 from t1 ORDER BY c1,c2 desc；--Using index; Using filesort，倒排违背索引顺序

--索引只用于where查询，但解决不了排序	
select * from t1 where c1=1 ORDER BY c1,c3;--Using index; Using filesort,
select * from t1 where c2=1 ORDER BY c1 ,c3;--Using where; Using filesort

5、有就更新无就插入

需要唯一键或者唯一索引，以下均支持批量。

• duplicate key
  返回值中，1代表插入成功，2代表更新成功，0代表值存在且更新前后值一样。返回值并不代表实际影响的行数，只是个标志而已。
  如：表中无下面两条数据，返回值为2
  表中有这两条数据且新值create_time和旧的值不一样，则返回2+2=4
  表中create_time和旧值一样，返回0

INSERT INTO `call_number` (
	`c_num`,
	`c_num`,
	`c_time`,
	`last_update_time`
)
VALUES
	(
		'156xxxx',
		'132xxxx',
		'2022-02-01',
		'2022-02-01'
	),(
		'156xxxx',
		'132xxxx',
		'2022-01-01',
		'2022-03-01'
	)ON DUPLICATE KEY UPDATE create_time=values(create_time),last_update_time=VALUES(last_update_time)

• insert ignore
  有就不操作，无就插入。但sql值有问题，也会被插入。如下面create_time传值为字母，依旧可以插进去。

INSERT ignore INTO `td_call` (
	`c_num`,
	`c_num`,
	`c_time`,
	`last_update_time`
)
VALUES
	(
		'156xxxx',
		'132xxxx',
		'2022-07-01',
		'2022-07-01'
	),
	(
		'156xxxx',
		'132xxxx',
		'2022-08-01',
		'2022-08-01'
	);

• replace into
  无则插入，有则删除在插入

REPLACE INTO `td_call` (
	`c_num`,
	`c_num`,
	`c_time`,
	`last_update_time`
)
VALUES
	(
		'156xxxx',
		'132xxxx',
		now(),
		now()
	);

6、字符串操作

• LOCATE(substr,str)
  查找子串在父串中第一次出现的位置从1开始计数，无则返回0

select locate('365','maomao365.com');--7
 select locate('365111','maomao365.com');--0

• position(substr in str)
  同上
• INSTR(str,substr)
  函数返回字符串中子字符串第一次出现的位置。如果在str中找不到子字符串，则INSTR()函数返回零(0)。默认不区分大小写。
  类似like

SELECT INSTR('MySQL INSTR', 'SQL');--3
SELECT INSTR('MySQL INSTR', 'sql');--3,不区分大小写
SELECT INSTR('MySQL INSTR', BINARY 'sql');--使用BINARY 可以区分大小写

效率问题，like >=INSTR。此函数看来无法优化like，功能类似上面两个了。

• LEFT (str, len)
  从左侧开始截取len长度的字符串，当len>length(str)时返回str。舍弃len后面的串

SELECT LEFT(12345678, 4) AS Left_Num;--1234
SELECT LEFT(12345678, 20) AS Left_Num;--12345678

• FIND_IN_SET(str,strlist)
  strlist必须是以,分割的字符串。任一个参数为NULL返回NULL，如果str不在strlist中返回0

SELECT FIND_IN_SET('abc','abcd,abc');--2
SELECT FIND_IN_SET('abc','abcd,abc,abc');--2
SELECT FIND_IN_SET('abc','abcd');--0
SELECT FIND_IN_SET('abc',null);--NULL

7、update select

• inner join

UPDATE t_task t
INNER JOIN (
  SELECT t.id,r.app_date
  FROM
  t_task t
  INNER JOIN t_record r ON t.id = r.object_id
  WHERE t.top = 1
  ORDER BY r.app_date DESC limit 1
) AS temp
ON t.id = temp.id AND t.id ='123' SET t.top =2