mysql

表字段

SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_schema = (SELECT database()) AND table_name='表名';

uuid

SELECT REPLACE(UUID(),'-','')

子串

locate("1", "234313") >0;

子串个数

length(order_num)-length(replace(order_num,'8',''))

字符串截取

substring_index(str,delim,count) 例: substring_index('www.abc.com','.',1) -> www

去除头尾字符

TRIM(BOTH 'x' FROM 'xxxbarxxx')

联表更新对应字段

UPDATE `user_info` ui RIGHT JOIN sys_user u ON u.user_name = ui.user_name
SET ui.id = u.id

保留两位小数

FORMAT(100.3465,2)

IF

IF(判断条件, 真时, 假时)
IFNULL(num, 0);

分组最新

SELECT * FROM (
	SELECT * FROM table1 t1
	WHERE 1=1
	HAVING 1
	ORDER BY create_time DESC) t1_latest
WHERE 1=1
-- GROUP BY t1_latest.type

获取父级全路径函数 1,11,111

BEGIN
	# 返回结果
	DECLARE parentIds varchar(1000) default '';
	# id临时变量
	DECLARE tempId varchar(32) default paramId ;

	WHILE (tempId is not null && tempId <> '') DO
		SET tempId = (SELECT p_id FROM ${tableName} WHERE id = tempId AND p_id <> id);
		IF (tempId is not null && tempId <> '') THEN
			SET parentIds = CONCAT(tempId, ',', parentIds);
		END IF;
	END WHILE;
	#字符处理
	IF parentIds <> '' THEN
		SET parentIds =  TRIM(TRAILING ',' FROM parentIds);
	END IF;

	return parentIds;
END

获取父级

SELECT
	t1._id id, t1.p_id, t1.sort
  -- ,t2.*
FROM(
	SELECT
		@r AS _id,
		( SELECT @r := p_id FROM ${tableName} WHERE id = _id ) AS p_id,
		@s := @s + 1 AS sort
	FROM
		( SELECT @r := ${id}, @s := 0 ) temp,
		${tableName}
	WHERE
		(@r IS NOT NULL AND @r !='')
	) t1
 -- LEFT JOIN ${tableName} t2 ON t1._id = t2.id 
ORDER BY
	t1.sort DESC

字符编码范围

• 汉字：[0x4e00,0x9fa5]（或十进制[19968,40869]）
• 数字：[0x30,0x39]（或十进制[48, 57]）
• 小写字母：[0x61,0x7a]（或十进制[97, 122]）
• 大写字母：[0x41,0x5a]（或十进制[65, 90]）

中文

SELECT * FROM {{表名}} where 1=1
and HEX(列名) REGEXP "e[4-9][0-9a-f]{4}"
-- and {{列名}} REGEXP "[^\x00-\xff]"