表字段
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‘,‘‘))
去除头尾字符
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);
字符编码范围
- 汉字:[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]"