1 FIND_IN_SET(str1,str2)
在str2中查询str1,存在返回大于0.不存在则返回0.
select find_in_set('1','0,1,2,3')
返回2
select find_in_set('11','0,1,2,3')
返回0
测试数据:
CREATE TABLE `person` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Email` varchar(255) DEFAULT NULL,
`Status` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `person` VALUES ('1', 'a@b.com', '1');
INSERT INTO `person` VALUES ('2', 'c@b.com', '1,2');
INSERT INTO `person` VALUES ('3', 'a@b.com', '2');
执行如下语句:
select * from person WHERE FIND_IN_SET('1',Status);
select * from person WHERE FIND_IN_SET(Status,'1,2');
揣测执行的算法:
将str2根据","才分为多个字符串,然后通过每一个字符串去全匹配str1.
可改写为IN
SELECT * FROM person WHERE Status IN ('1','2');
IFNULL
IFNULL(字段名,替补值)
如果字段的值为nulll,将会用替补值代替,但是记录查询不到的情况下还是会返回null。
如下SQL:
select IFNULL(name,'德玛')
from user
where id =#{id}
如果存在id为1的记录,name字段为null,将会用德玛代替,如果不存在id为1的记录,将会返回null。
返回子字符串在大字符串中的位置,参考: https://www.jb51.net/article/143105.htm
SELECT FIND_IN_SET("b", "a,b,c,d");
-- 字符串拼接
SELECT CONCAT("My", NULL, "SQL");
mysql 正则表达式 https://www.cnblogs.com/richerdyoung/p/10214953.html
select * from test01 where child_id REGEXP CONCAT("(",REPLACE("1,2,4",",","|"),")")
-- 和如下相同
select * from test01 where ( child_id LIKE "%1%" OR child_id LIKE "%2%" OR child_id LIKE "%4%")
字符串拼接
SELECT CONCAT("My", NULL, "SQL");
字符串替换
SELECT REPLACE("abcd","cd","ef")
获取数据库当前毫秒数
select REPLACE(unix_timestamp(current_timestamp(3)),'.','');
不为空判断
name is not null and LENGTH(trim(name))>0
参考:https://www.cnblogs.com/blogxiao/p/9251460.html