MySQL常用函数

一、截取字符串
update sheet0 set
有效期至 = CONCAT(SUBSTRING(TRIM(有效期至),1,4),"/",SUBSTRING(TRIM(有效期至),5,2),"/",SUBSTRING(TRIM(有效期至),7,2))
where LENGTH(TRIM(有效期至))=8 AND LOCATE(’/’,有效期至)=0; #有效期至等于8位。LOCATE(’/’,有效期至)=0；意思是不包含”/“

update sheet0 set
有效期至 = CONCAT(SUBSTRING(TRIM(有效期至),1,4),"/",SUBSTRING(TRIM(有效期至),5,2),"/",“01”)
where LENGTH(TRIM(有效期至))=6 AND LOCATE(’/’,有效期至)=0; #有效期至等于6位 后补上’/01’。

select LOCATE(’/’, ‘2023131’); #0
select LOCATE(’/’, ‘2022/9/4’); #5
select INSTR(‘202294’, ‘/’); #5
select INSTR(‘2022/9/4’, ‘/’); #5
相关函数：TRIM(str), SUBSTRING(str,n[从什么位置开始截取],num[需截取的字符个数]) n从1开始,

1、生成序列号
SELECT (@rowNO := @rowNo+1) as rowno, a.* FROM sheet0 a,(SELECT @rowNO :=0) b;
1.1、生成序列号
set @rowNO := 0;
SELECT (@rowNO := @rowNo+1) as rowno, a.* FROM sheet0 a;
2、新增id
set @rownum= 0;
UPDATE sheet0
SET id = (
SELECT @rownum := @rownum +1 as nid)
WHERE id is null;
3、新增uuid
UPDATE sheet0 SET id = (SELECT replace(UUID(),’-’,’’)) WHERE id is null;

二、四舍五入
SELECT ROUND(3.45,1); #3.5 四舍五入
SELECT FORMAT(12332.255,2); #12,332.26 四舍五入
SELECT truncate(23.336,2); #23.33 直接截取
SELECT convert(4545.5667,decimal); #4546 四舍五入，取整