Mysql常见函数及用法-字符串函数

1、字符串函数

1.1 主要用到的表结构

-- 创建表
CREATE TABLE `character_study` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `utf8` varchar(20) DEFAULT NULL,
  `utf8mb4` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL,
  `gbk` varchar(20) CHARACTER SET gbk DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- 本表结构为了便于演示，故字段类型各有不同，仅仅做学习用

1.2、length / char_length

1.2.1 作用及区别

作用： 返回指定字符串的长度
区别： 主要在于汉字和非汉字的区别，汉字在mysql中，一个汉字在不同的字段类型下，占用的字节数不同，UTF-8模式下，一个汉字占用3个字节，GBK模式下，一个汉字占用2个字节

• length ：返回对应的字节数
• char_length:返回对应的字符数

1.2.2 示例

-- 初始化数据
INSERT INTO character_study(utf8,utf8mb4,gbk)VALUES('中','中','中');
INSERT INTO character_study(utf8,utf8mb4,gbk)VALUES('abc','abc','abc');
-- 分别查询数据
SELECT id,LENGTH(utf8),LENGTH(utf8mb4),LENGTH(gbk),CHAR_LENGTH(utf8),CHAR_LENGTH(utf8mb4),CHAR_LENGTH(gbk) FROM `character_study`;

以上执行结果如下：

1.3、left / right

1.3.1 作用及区别

作用：从左/右截取指定长度的字符
区别：从函数名即可判定，一个从左截取指定字符，一个从右开始截取指定字符

1.3.2 示例

SELECT LEFT('我和我的祖国一刻也不能分割',3); -- 我和我 
SELECT RIGHT('我和我的祖国一刻也不能分割',3); -- 能分割

注意： 截取的字符必须为大于1的正数，若小于1，则查询结果直接返回空字符串

1.4、substr / substring / substring_index

1.4.1 作用及区别

作用：从指定位置截取自定个数的字符串
区别：从函数名即可判定，一个从左截取指定字符，一个从右开始截取指定字符

• SUBSTR(s, start, length)：从字符串 s 的 start 位置截取长度为 length 的子字符串（可与SUBSTRING替换）
• SUBSTRING(s, start, length)：从字符串 s 的 start 位置截取长度为 length 的子字符串（可与SUBSTR替换）
• SUBSTRING_INDEX(s, delimiter, number) ：返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串； 如果 number 是正数，返回第 number 个字符左边的字符串；如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。(按语言（javascript）的角度，就是通过delimiter将字符串进行一个split生成一个数组，然后再将数组内指定number个数通过delimiter进行一个join)

1.4.2 示例

SELECT SUBSTR('我和我的祖国一刻也不能分割' , 5 , 4); -- 祖国一刻
SELECT SUBSTR('我和我的祖国一刻也不能分割' FROM 5 for 4);-- 祖国一刻
SELECT SUBSTRING('我和我的祖国一刻也不能分割' , -6 , 4);-- 刻也不能
SELECT SUBSTRING('我和我的祖国一刻也不能分割' from -6 for 4) -- 刻也不能
SELECT SUBSTRING_INDEX('123.456.789','.',1) -- 123
SELECT SUBSTRING_INDEX('123.456.789','.',-1) -- 789

1.5、concat / group_concat / concat_ws

1.5.1 作用及区别

作用：字符串的拼接
区别：

• concat (s1,s2…sn)：字符串 s1,s2 等多个字符串合并为一个字符串
• concat_ws(separator, str1, str2, …)，将字符串s1.s2，通过separator字符进行连接，concat 函数可认作特殊的concat_ws，其中separator为空字符
• group_concat(field):将制定字段通过逗号分隔，合并为一个字符，此函数会将查询结果进行聚合，若无group by语句，则只会返回一条数据

1.5.2 示例

SELECT CONCAT('1','20'); -- 120
SELECT CONCAT_ws('-','1','20'); -- 1-20

相关函数也可以嵌套使用，如下

1.6、find_in_set / locate / position

1.6.1 作用及区别

作用：字符串的定位
区别：

• find_in_set (str,strlist): 判断strList中是否包含str，其中strlist以逗号分隔，逗号分隔拆分的数据为一个元素
• LOCATE(substr,str,pos):返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0,其中pos 不传则默认为0
• POSITION(substr IN str) ：返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在，返回值为 0，与LOCATE方法一致

1.6.2 示例

SELECT FIND_IN_SET('a','a,b,c'); 	-- 1
SELECT LOCATE('a','abcadef');			-- 1
SELECT LOCATE('a','abcadef',2);		-- 4
SELECT POSITION('a' IN 'abcadef') -- 1

1.7、lower / lcase / upper / ucase

1.7.1 作用及区别

作用：字符串大小写转换
区别：

• LOWER(str)：大写转换为小写
• LCASE(str)：大写转换为小写，与LOWER作用一样
• UPPER(str): 小写转换为大写
• UCASE(str)：小写转换为大写，与UPPER作用一样

1.7.2 示例

SELECT LCASE('Abc'); -- abc
SELECT LOWER('Abc'); -- abc
SELECT UPPER('Abc'); -- ABC
SELECT UCASE('Abc'); -- ABC

1.8、replace

1.8.1 作用

作用：将某个字符内的指定字符串替换另外一个字符串
REPLACE(str,from_str,to_str):将str字符串内的from_str字符串替换为to_str 字符串

1.8.2 示例

SELECT REPLACE('我爱中国','爱','love'); -- 我love中国