标题


为了方便自己学习专门整理了mysql中操作字符串的一些列方法,分享给大家共勉。
这一块内容挺多,花费了几天整理,难免有遗漏的地方,欢迎个位老板批评指正,以待完善。
另外注意领悟函数间的套用会给你不一样的玩法,锻炼思维,sql函数很多也很活,注意合理的搭配使用。里面还有些内容未完工先发表,之后有时间在处理。

关于mysql中处理字符串的

其他函数

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

GROUP BY中使用WITH ROLLUP

在这里插入图片描述


CREATE TABLE `test` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(25) DEFAULT NULL COMMENT '标题',
  `uid` int(11) DEFAULT NULL COMMENT 'uid',
  `money` decimal(2,0) DEFAULT '0',
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('2', '国庆节', '2', '12', '周伯通');
INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('3', '这次是8天假哦', '3', '33', '老顽童');
INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('4', '这是Uid=1的第一条数据哦', '1', '70', '欧阳锋');
INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('5', '灵白山少主', '4', '99', '欧阳克');
INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('7', '九阴真经创始人', '3', '12', '小顽童');
INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('8', '双手互博', '2', '56', '周伯通');
INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('9', '销魂掌', '2', '19', '周伯通');
INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('10', '蛤蟆功', '1', '57', '欧阳锋');
-- INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('11', '绝杀掌', '3', '800', '小顽童');
-- INSERT INTO `test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('12', '九阴真经', '3', '84', '老顽童');

WITH ROLLUP 跟 order by 从某种意义来说就是相违背的,WITH ROLLUP 多产生一条数据,这条数据按道理是不参与排序的,order by 又是对结果集的排序,所以二者是不能同时使用,当然了你就要用,在满足需求的条件下可以使用子查询 使用

 SELECT name, SUM(money) as money FROM  test GROUP BY name WITH ROLLUP 
 
# 这个排序感觉意义不大
 select * from (SELECT name, SUM(money) as money FROM  test 
 GROUP BY name WITH ROLLUP) a order by a.money desc;

下面想法想都别想,大错特错,那是压根没理解 with rollup
子查询已经聚合好了,你还聚合,就是在每个分组记录之后在增加一条记录
在这里插入图片描述

HAVING

在这里插入图片描述

关于行号的使用

业务场景:有一个问题表,里面有很多历史数据,后来业务需要增加一个问题序号,问题序号需要唯一自增,现在只能保证后来新添加的问题有唯一自增序号,之前的历史数据是为null的,这个时候需要把之前的历史数据赋值问题编号从1开始自增

-- 这个sql会把数据库字段problem_no管你现在有没有值 都从1开始往后自增赋值
update t_problem tcp,
 (select
 id,@r := @r +1 as num
 from t_problem ,(select @r:=0) n) tmp
 set tcp.problem_no = tmp.num
 where tcp.id = tmp.id;

# 其中子查询其实就是增加行号的通用写法
select  id,@r := @r +1 as num  from t_problem ,(select @r:=0) n
# 多表联查不想写子查询怎么加行号
select  @r := @r +1 as id, u.username,b.bankname,v.datetime
from (select @r:=0) n,user_info u
left join bank_info b on b.code = u.code
left join v_info v on u.no = v.no 

Mysql 判断字段是否包含字母(大小写)

-- 使用正则表达式来判断字段是否包含字母,不区分大小写 'a-z’或者’A-Z’都满足
select * from 表名 where 字段名 REGEXP '[A-Z]';

-- 区分大小写
select * from 表名 where 字段名 REGEXP BINARY '[A-Z]';//只查包含大写字母的字段
select * from 表名 where 字段名 REGEXP BINARY '[a-z]';//只查包含小写字母的字段

mysql排序的方法

关于mysql中的排序是很灵活的,可以借助很多函数来实现排序,也可以构造出一个或者多个字段来进行排序,没有办不了的排序,只有你想不到的思想方法。下面主要介绍排序注意点及排序中遇到null字段排在最后。

MySQL(多个)字段排序问题踩坑点
排序前请确认好类型一致性
① 字符串数字排序务必转成数字类型后排序
② 字符串日期排序务必转成日期类型后排序或者直接使用 unix_timestamp(字符串日期)
③ 排序时候遇到中文建议: order by convert(name using gb2312) asc;
④ 使用FIELD最好做空值判断 FIELD(ifnull(is_true,‘other’),‘1’,‘2’,‘other’);

使用order by record_time desc实现降序时,record_time 为null数据的会排在数据的最后面;
但是,order by record_time升序时,record_time 为null的数据则会排在最前面,如果想要将record_time
为null的数据排在最后,就需要加上is null。

也可以在 MySQL 中使用 ORDER BY 子句来实现.首先按 NULL 排序,然后按日期排序;
mysql 按时间降序排序、时间为空的排在前面,IF(ISNULL(time), 0 ,1) 使用
order by IF(ISNULL(entry_park_time), 0 ,1), entry_park_time desc




 -- 核心思想把null值给整到最后,再排有值得字段
select  date(submit_audit_time)  from t_bank_limit ORDER BY IF(ISNULL(submit_audit_time), 1 ,0) ,submit_audit_time;
select  date(submit_audit_time)  from t_bank_limit ORDER BY IF(ISNULL(submit_audit_time), 1 ,0) ,submit_audit_time desc;
 
 
select  submit_audit_time from t_bank_limit ORDER BY submit_audit_time is null,submit_audit_time;
select  submit_audit_time from t_bank_limit ORDER BY submit_audit_time is null,submit_audit_time desc;
 
-- 这个就是升序的时候 把没日期赋值到最大日期,正序排就自动到末尾了,但是你的保证 库里的时间小于now()
-- 否则会出错 ,保险点把now()换成子查询,取数据库最大日期加一天,但是就繁琐了没必要,不建议使用
select  submit_audit_time from t_bank_limit ORDER BY ifnull(submit_audit_time,now())  ;
select  submit_audit_time from t_bank_limit ORDER BY submit_audit_time desc  ;

MySQL支持实现group_concat() 内部自定义排序:group_concat函数支持order by内部排序
SELECT uid, GROUP_CONCAT(money ORDER by money DESC) AS money_desc FROM reward GROUP BY uid DESC;
SELECT uid, GROUP_CONCAT(id ORDER by money DESC) AS id_money_desc FROM reward GROUP BY uid DESC;


另外我们还可以使用contat函数把多个字段拼接起来,在进行排序。但是要保证字段不能为nullselect * from a order by concat(code,name) desc
 order by concat(code, name) desc的效果等同于 order by code desc, name desc
 
 string类型的时间来排序
 我的时间格式是 2021/5/5 1:00:00 这种的string类型的日期 如果直接 ORDER BY 数据会乱 
 使用ORDER BY  unix_timestamp(要排序的字段) DESC
 

 


mysql获取字符串长度的方法

length(str):计算字段的长度 一个汉字是算三个字符,一个数字或字母算一个字符。
CHAR_LENGTH(str): 返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为 10,而CHAR_LENGTH()的返回值为5。
CHARACTER_LENGTH(str) CHARACTER_LENGTH()是CHAR_LENGTH()的同义词。
BIT_LENGTH(str) 返回2进制长度.
LENGTH(str)/OCTET_LENGTH(str):字节数
CHAR_LENGTH(str)/CHARACTER_LENGTH(str):字符数

# 常用的英文字母被编码成1个字节,汉字通常是3个字节,只有很生僻的字符才会被编码成4-6个字节。
# LENGTH(str)和OCTET_LENGTH(str)函数用于返回字符串的字节长度,例如:
select OCTET_LENGTH('年')        -- 3
select LENGTH('年') from table_name;        -- 3
# 另外,CHAR_LENGTH(str)和CHARACTER_LENGTH(str)函数用于返回字符串的字符长度,
#也就是字符个数。例如
select CHAR_LENGTH('年') from table_name;        -- 1
select CHARACTER_LENGTH('年') from table_name;        -- 1
# BIT_LENGTH(str)函数用于返回字符串的比特长度(比特数量),例如:
# 一个字节包含 8 个比特。
select BIT_LENGTH('年') from table_name;        -- 24

简单的总结来说,mysql中获取字符串长度的有两个函数:
length:返回字符串所占的字节数,是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符
char_length:返回字符串所占的字符数,不管汉字还是数字或者是字母都算是一个字符
高级应用:
length()<>char_length(),可以用来检验是否含有中文字符。
utf-8编码中判定某个字段为全英文,length(字段) = char_length(字段)即可。

课外补充:
CHARACTER_MAXIMUM_LENGTH以字符为单位的最大长度,适于二进制数据、字符数据,或者文本和图像数据。
CHARACTER_OCTET_LENGTH以字节为单位的最大长度,适于二进制数据、字符数据,或者文本和图像数据。

MySQL中连接字符串常用函数

MySQL中concat函数的用法(连接字符串)

这个没啥好说的,但是需要注意连接null时的情况:
使用方法: CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。

select CONCAT('2022','年') from table_name;        -- 2022年
select CONCAT('2022','年',null) from table_name;    --  空
# 对于字符串常量,我们也可直接将它们连写在一起。
SELECT 'MySQL' '字符串' '函数' AS str; -- MySQL字符串函数

# 以上方式只能用于连接字符串常量,不能用于连接字段的值。

📝如果启用了 SQL 模式 PIPES_AS_CONCAT,
MySQL 逻辑或运算符(||)也可以用于连接字符串 ,类似于 Oracle 和 PostgreSQL。

还需要注意一下下面这种情况:
如果所有参数均为非二进制字符串,则结果为非二进制字符串。
如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast, 例如:

SELECT CONCAT(CAST('110' AS CHAR),CAST('120' AS CHAR)) from table_name; -- 110120

SELECT CONCAT('110','120') from table_name;    -- 110120
补充一下cast()函数

https://blog.csdn.net/qq_21101587/article/details/78642423
注意点:
其中DATE支持的范围是: 1000-01-01 至 9999-12-31,(实验版本为:)
如果为: 999-01-01 结果则会0999-01-01。
如果为: 01-01-01则会为:2001-01-01。

CAST函数中expr的值可以转换为type,其转换的结果才正确,否则转换后的结果默认值,如Null,0等。
例如:一个Char类型转换为Demical类型,转换的结果就为0。

select cast('999-11-11' as DATE); -- 0999-11-11
select cast('01-11-11' as DATE);  -- 2001-11-11
select cast(' 1000-01-01 ' as DATE); -- 1000-01-01  -- 首尾空格无影响
select cast('9999-12-31' as DATE);  -- 9999-12-31

SELECT CAST('ANDYQIAN' AS DECIMAL); -- 0

如何判断左右两边都是相同类型的,比如都是字符串,则以字符串进行对比。如果是数字,则以数字进行比较。

# 其实下面这些字符串得到值就是 CAST()函数转数值得到的答案
select  1='1'; -- 1
select  1='  1'; -- 1
select  1='1A'; -- 1
select  1=' c 1A'; -- 0
select  1='1 '; -- 1 
select  1='1 6'; -- 1
select  1='   1 6'; -- 1
select  -1='   -1 6'; -- 1
MySQL 认为数字1'1','1_','1A','  1' 相等,故无法通过索引二分查找准确定位到具体的值。
select cast(' c 1A' as UNSIGNED); -- 0
select cast('   1 6' as UNSIGNED); -- 1
select cast('-1 6' as UNSIGNED); -- 18446744073709551615
select cast('   -1 6' as UNSIGNED) ;-- 18446744073709551615
select cast('-1' as UNSIGNED) ;-- 18446744073709551615
select cast('-1' as SIGNED) ;-- -1
select cast('   -1 6' as SIGNED); -- -1
SQL中的cast和convert区别

mysql为我们提供了两个类型转换函数:CAST和CONVERT
CAST() 和CONVERT() 函数可用来获取一个类型的值,并产生另一个类型的值。
这个类型 可以是以下值其中的 一个:

类型含义
BINARY[(N)]二进制,同带binary前缀的效果
CHAR[(N)]字符型,可带参数
DATE日期
DATETIME日期时间型
DECIMAL浮点数
SIGNED [INTEGER]整数
TIME时间
UNSIGNED [INTEGER]无符号整数

select cast('2015-11-03 15:31:26' as  BINARY) as date  ; -- 2015-11-03 15:31:26
select cast('2015-11-03 15:31:26' as  signed) as date  ; -- 2015
-- signed 后面还有个INTEGER   可以不写的  
select cast('2015-11-03 15:31:26' as  signed INTEGER ) as date  ; -- 2015

select cast('2015-11-03 15:31:26' as UNSIGNED) as date  ; -- 2015
select cast('2015-11-03 15:31:26' as UNSIGNED INTEGER) as date  ; -- 2015

select cast('125e342.83' as signed); -- 125
select cast('3.35' as signed); -- 3
select cast('3*35' as signed); -- 3
select cast('3.35' as UNSIGNED); -- 3
select cast('3.35' as UNSIGNED INTEGER); -- 3
SELECT CAST("12321" as UNSIGNED) AS result -- 12321

select cast(' c 1A' as UNSIGNED) -- 0
select cast('   1 6' as UNSIGNED) -- 1
select cast('-1 6' as UNSIGNED) -- 18446744073709551615
select cast('   -1 6' as UNSIGNED) -- 18446744073709551615
select cast('-1' as UNSIGNED) -- 18446744073709551615
select cast('-1' as SIGNED) -- -1
select cast('   -1 6' as SIGNED) -- -1


select cast('2015-11-03 15:31:26' as  char) as date  ; -- 2015-11-03 15:31:26
select cast(1995 as char) as result -- 1995
select cast('2015-11-03 15:31:26' as  datetime) as date  ; -- 2015-11-03 15:31:26
select cast('2015-11-03 15:31:26' as  date) as date  ; -- 2015-11-03
select cast('2015-11-03 15:31:26' as  time) as date  ; -- 15:31:26

解释:decimal 数据类型最多可存储 38 个数字,所有数字都能够放到小数点的右边。
decimal 数据类型存储了一个准确(精确)的数字表达法; 不存储值的近似值。
其中10是小数点左边和右边的数字个数之和(不包括小数点),2代表小数点右边的小数位数或数字个数。
decimal(10,2)可以存储8位整数2位小数的数字。
#四舍五入式保留小数
select cast(20 as decimal(10, 2)) as num ; -- 20.00
select cast(20.0 as decimal(10, 2)) as num ; -- 20.00
select cast(20.00 as decimal(10, 2)) as num ; -- 20.00
select cast(20.01 as decimal(10, 2)) as num ; -- 20.01
select cast(20.08 as decimal(10, 2)) as num ; -- 20.08
select cast(20.044 as decimal(10, 2)) as num ; -- 20.04
select cast(20.046 as decimal(10, 2)) as num ; -- 20.05
select cast(20.056 as decimal(10, 2)) as num ; -- 20.06
select cast(20.0569 as decimal(10, 2)) as num ; -- 20.06

# 计算百分比也可以如下
select cast((1/3)*100 as UNSIGNED) as percent ;  -- 33 
select cast((1/2)*100 as UNSIGNED) as percent  ; -- 50  
select cast((1/2)*100 as decimal(10, 4)) as percent ;-- 50.00
 

其实cast和conver以上效果都是一样的 只不过 conver有个方法可以设置编码格式

所以我们也可以用CAST解决问题:

select server_id from cardserver where game_id = 1 order by CAST(server_id as SIGNED) desc limit 10

也可以使用CONVERT来搞定此问题:

select server_id from cardserver where game_id = 1 order by CONVERT(server_id,SIGNED) desc limit 10
实践应用
需求:查询下一个待插入的问题最大编号。 这里problem_no如果是int类型就不会这么麻烦,这里数据库设计的是varch类型,然后 max(problem_no) 出来的字符串数字不对,需要转换成数字比较后是我们想要的

select max(cast(ifnull(problem_no,0) as UNSIGNED INTEGER) + 1 ) as problemNo from t_common_problem

mysql中文排序失效解决方法。
使用CONVERT(expr USING transcoding_name)

 CONVERT(expr USING transcoding_name)  
 SELECT CONVERT('abc' USING utf8); -- abc    abc使用utf8编码
mysql中文排序失效解决方法。将字段先转换成gbk编码再排序:
前提是mysql安装了gbk字符集,否则会报错。
convert(字段名 USING gbk)mysql 
[更多方法](http://www.javashuo.com/article/p-yrztiqbe-ha.html)
select * from sys_dept ORDER BY convert(dept_name USING gbk);
select * from sys_dept ORDER BY convert(dept_name USING utf8);
select * from sys_dept -- ORDER BY convert(sys_dept USING utf8);

将Int 转为varchar经常用 concat函数,比如concat(8,‘0’) 得到字符串 ‘80’

MYSQL隐式转换问题

字符串数字,跟数组间的隐式转换

有时候由于mysql底部隐式转换,使得你的结果看似是正确的,但是当超出某个范围,或者在某个条件下会使得答案出现问题。

今天主要一个核心思想,你使用的这个函数到底是操作字符串还是针对数值,是什么就用人家指定类型,别太依赖人家的隐式转换,使用CAST()或CONVERT()函数 来转成想要的格式,进而规避不必要的问题。

当然了你要是设计表时就把字段类型百分比设计对就能在99%规避掉。

如:案例1

注:`id` varchar(255) DEFAULT NULL (这就是个不合理设计,不是bigint 不是not null 不是主键) 虽然不合理,但那不是我们关心的,注意看下面我们查出了两条数据,
select * from t1 where id=204027026112927603;
+--------------------+
| id   |
+--------------------+
| 204027026112927605 |
| 204027026112927603 |
+--------------------+
# id唯一为什么查出来不是一条 这就是隐式转换带来的问题,所以说解决方法也就有了,
数据类型保持一致即可,有以下思路
select * from t1 where id = '204027026112927603';
select * from t1 where  CAST(id as UNSIGNED) = 204027026112927603;

select '190325171202362936' = 190325171202362931; -- 1
select '190325171202362936' = '190325171202362931'; -- 0

string类型的数字转成doule类型超过16位后溢出,这里经过测试,**当数字超过16位以后,转成double类型就已经不准确了,**例如20402702611292711会表示成20402702611292712

案例2:

使用以下函数不是数值类型,会出错
在这里插入图片描述

需求:查询下一个待插入的问题最大编号。 这里problem_no
如果是int类型就不会这么麻烦,
这里数据库设计的是varch类型,然后 max(problem_no) 出来的字符串数字不对,
需要转换成数字比较后是我们想要的

 select max(cast(ifnull(problem_no,0) as UNSIGNED INTEGER) + 1 ) 
 as problemNo  from t_common_problem

案例3:数据格式前后不一致可能引起索引失效(隐式转换,不走索引)

MySql整型索引和字符串索引

USER_ID varchar(11) DEFAULT NULL COMMENT '用户账号',
 EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111; -- 索引失效
 EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111'; -- 使用索引
 -- varchar类型的字段在作为字符串查询的时候使用了索引,在以数值类型进行查询时是不使用索引的。
  USER_ID int(11) DEFAULT NULL COMMENT '用户账号',
  EXPLAIN SELECT * FROM test_user WHERE USER_ID = 111; -- 使用索引
  EXPLAIN SELECT * FROM test_user WHERE USER_ID = '111';-- 使用索引
 -- int类型的字段无论是以字符串查询还是以数值型查询都会走索引。

在这里插入图片描述

  1. 当我们使用的字段是数值类型时,加引号或者不加引号(sql中单引号和双引号实现相同效果)都不影响索引的使用
  2. 当我们的字段是字符串类型时,不加引号的查询无法使用索引,加引号的查询才可正常使用索引

MySQL中concat_ws函数

使用方法: CONCAT_WS(separator,str1,str2,…)
CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。
注意:
如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL。


select concat_ws(',','11','22',NULL); -- 11,22
select concat_ws(null,'11','22',NULL); -- 空
# 通俗来讲就是往中间待拼接字符串末尾添加指定字符(串)
select concat_ws('1','1','2','3','4'); -- 1121314
select concat_ws('1','1','2','3','4',null); -- 1121314
select concat_ws('1','1','2','3','4',''); -- 11213141
select concat_ws('ccc','1','2','3','4'); -- 1ccc2ccc3ccc4
SELECT CONCAT_WS('-', 'MySQL', NULL, '字符串') AS str1, -- MySQL-字符串

MySQL中group_concat函数

完整的语法如下:
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])

# 以id分组,把name字段的值打印在一行,逗号分隔(默认)
select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

# 上面默认写法以逗号分隔,现在要是变换为自己想要的分隔符 如分号
select id,group_concat(name separator ';') from aa group by id;
# 如果你仅仅想字段结果去重
select id,group_concat(distinct name) from aa group by id;
# 如果你仅仅想字段结果排序
select id,group_concat(name order by name desc) from aa group by id;

# 那现在综合写法就是
select id,group_concat(distinct name order by name desc separator ';') from aa group by id;

# 如果你想在每个后面加上你想要的字符(指定分隔符合并,例如指定使用 ;  符号进行合并)
select id,group_concat( name ,';') from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10;,20;,20;|
|2 | 20; |
|3 | 200;,500;|
+------+--------------------+

repeat()函数

用来复制字符串,如下’ab’表示要复制的字符串,2表示复制的份数

select repeat('ab',2); -- abab

MYSQL字符串大小写转化:LOWER()、UPPER()

LOWER(str)和LCASE(str)函数用于将字符串转换为小写形式,例如:
UPPER(str)和UCASE(str)函数用于将字符串转换为大写形式,例如:

# 小写形式  结果都是:mysql字符串函数
SELECT LOWER('MySQL字符串函数') AS str1,
 LCASE('MySQL字符串函数') AS str2;
# 大写形式 结果都是:MYSQL字符串函数
SELECT UPPER('MySQL字符串函数') AS str1, UCASE('MySQL字符串函数') AS str2;

MySQL 大小写转换函数不支持二进制字符串(BINARY、VARBINARY、BLOB)),可以将其转换为非二进制的字符串之后再进程处理。例如:

# MySQL字符串函数  mysql字符串函数
SELECT LOWER(BINARY 'MySQL字符串函数') AS str1,
LOWER(CONVERT(BINARY 'MySQL字符串函数' USING utf8mb4)) AS str2;

MySQL 字符串拆分操作(含分隔符的字符串截取)

(此处利用 mysql 库的 help_topic 表的 help_topic_id 来作为变量,因为 help_topic_id 是自增的,当然也可以用其他表的自增字段辅助。)

SELECT LEFT(SUBSTRING('P123',help_topic_id+1),1) AS num 
FROM mysql.help_topic 
WHERE help_topic_id < LENGTH('P123');

+------+--------------------+
| num | 
+------+
|P |
|1 |
|2 |
|3 |
+------+

select bank_name from table_name 
WHERE enquiry_odd = '11111111111';  -- 建设银行,中信银行,中国银行
 
select SUBSTRING_INDEX(SUBSTRING_INDEX(m.bank_name,',',h.help_topic_id+1),',',-1) as bn
from table_name m 
LEFT JOIN mysql.help_topic h 
on h.help_topic_id < (LENGTH(m.bank_name)-LENGTH(REPLACE(m.bank_name,',',''))+1)
WHERE  m.enquiry_odd = '11111111111';

+------+--------------------+
| bn| 
+------+
|建设银行 |
|中信银行 |
|中国银行 |
+------+


SELECT 
 SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM 
 mysql.help_topic 
WHERE 
 help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1
+------+--------------------+
| bn| 
+------+
|7654 |
|7698|
|7782|
|7788|
+------+


MYSQL字符串截取

这块length不给默认到字符串结束位置,给的话不能为负数(大于0的整数),不然就是空串没啥意义了。
字符串截取:SUBSTRING(str,pos,length)、SUBSTRING(str,pos)
支持以下 4 种形式
SUBSTRING(str,pos)、SUBSTRING(str FROM pos)、SUBSTRING(str,pos,len)以及SUBSTRING(str FROM pos FOR len)
注:str为要操作的字符串 、pos 为开始截取的位置(必须大于0小于字符串长度 否则为空字符串),
当pos为负数表示从右边位数开始截取、
最后一位参数表示截取的长度,大于0才有意义否则为空字符
不给默认为截取到字符串最后。

select SUBSTRING('ABCD',0) -- '' 这个空
select SUBSTRING('ABCD',1) -- ABCD
select SUBSTRING('ABCD',2) -- BCD
select SUBSTRING('ABCD',4) -- D
select SUBSTRING('ABCD',5) -- '' 这个空

select SUBSTRING('ABCD',-0); -- '' 这个空
select SUBSTRING('ABCD',0); -- '' 这个空
select SUBSTRING('ABCD',-1); -- D
select SUBSTRING('ABCD',-2); -- CD
select SUBSTRING('ABCD',-4); -- ABCD
select SUBSTRING('ABCD',-5); -- '' 这个空

# 第三个参数是截取几个字符
select SUBSTRING('ABCD',1,3) -- ABC
select SUBSTRING('ABCD',1,0) -- '' 这个空
# 下面例子更清晰
SELECT SUBSTRING('MySQL字符串函数', -2) AS str1,
       SUBSTRING('MySQL字符串函数', -5, 3) AS str2;
str1  |str2  |
------+------+
函数  |字符串 |


按关键字截取字符串:SUBSTRING_INDEX(str,split,length)
如果在字符串中找不到 split参数指定的值,就返回整个字符串

str 需要拆分的字符串
split分隔符,通过某字符进行拆分
count 当 count 为正数,取第 n 个分隔符之前的所有字符; 当 count 为负数,取倒数第 n 个分隔符之后的所有字符。

# 获取第2个以“,”逗号为分隔符之前的所有字符。
select SUBSTRING_INDEX('123,456,789,110',',',2) -- 123,456
# 获取倒数第2个以“,”逗号分隔符之后的所有字符
select SUBSTRING_INDEX('123,456,789,110',',',-2) -- 789,110
# 找不到 split参数指定的值,就返回整个字符串
select SUBSTRING_INDEX('123,456,789,110',';',-2) -- 123,456,789,110
# 获取第一位
SELECT SUBSTRING_INDEX('c-11065-50-22','-',1);-- c
# 获取最后一位
SELECT REVERSE(SUBSTRING_INDEX(REVERSE('c-11065-50-22'),'-',1)); -- 22
# 获取中间指定位置 (第n分割符后的 n+1前的)
SELECT SUBSTRING('c-11065-50-22',2+1,8-2-1);-- 11065
# 获取指定位置后的所有 
SELECT SUBSTRING('c-11065-50-22',2+1);-- 11065-50-22
# 获取-出现1到3次时的下标  2 8 11
select 
case when 
(length('c-11065-50-22')-LOCATE(REVERSE('-'),REVERSE('c-11065-50-22')) + 1) < (length(SUBSTRING_INDEX('c-11065-50-22','-',3))+1)
THEN -1 ELSE (length(SUBSTRING_INDEX('c-11065-50-22','-',3))+1) end as indexOfNum



从左或右边开始截取字符串 LEFT(str, length)、RIGHT(str, length)
str:被截取字段,length:截取长度

SELECT LEFT('MySQL字符串函数',5) AS str1,
       RIGHT('MySQL字符串函数',5) AS str2;
str1 |str2     |
-----+---------+
MySQL|字符串函数|

SUBSTR()和MID()函数都是 SUBSTRING() 函数的同义词
SUBSTR()和MID()函数都是 SUBSTRING() 函数的同义词,也支持以上 4 种形式。
基本语法是这样的:MID(str,pos,len)、string substr(string, start, length) 最后一位参数可选
这里,str是字符串,pos是起始子字符串的位置,len是一个可选参数,它决定从起始位置返回的字符数。

SELECT MID('I drink coffee', 3) Result; -- drink coffee
SELECT substr('I drink coffee', 3) Result; -- drink coffee
SELECT substring('I drink coffee', 3) Result; -- drink coffee

SELECT MID('I drink coffee', 3, 5) Result; -- drink
SELECT substr('I drink coffee', 3, 5) Result; -- drink
SELECT substring('I drink coffee', 3, 5) Result; -- drink

# 使用FROM子句
SELECT MID('I drink coffee' FROM 3) Result; -- drink coffee
SELECT substr('I drink coffee' FROM 3) Result; -- drink coffee
SELECT substring('I drink coffee' FROM 3) Result; -- drink coffee


# 指定长度(使用FOR子句)
SELECT MID('I drink coffee' FROM 3 FOR 5) Result; -- drink
SELECT substr('I drink coffee' FROM 3 FOR 5) Result; -- drink
SELECT substring('I drink coffee' FROM 3 FOR 5) Result; -- drink
select DATABASE() ; -- admin
# 查看数据库名第一位
select MID(DATABASE(),1,1); -- a

select substr(DATABASE(),1,1); -- a

典型应用:截取最后一个.后缀 获取文件属性

# 获取文件名后缀
select REVERSE(left(REVERSE('139_79.75x500.jpg')
,LOCATE('.',REVERSE('139_79.75x500.jpg'))-1)) as fileType;

select if(SUBSTRING_INDEX('139_79.75x500.jpg', '.', -1)='139_79.75x500.jpg',
null,SUBSTRING_INDEX('139_79.75x500.jpg', '.', -1)) as fileType;

select REVERSE(left(REVERSE('139_79.75x500.jpg'),
INSTR(REVERSE('139_79.75x500.jpg'),'.')-1)) as fileType;
 # 推荐
select RIGHT('139_79.75x500.jpg',INSTR(REVERSE('139_79.75x500.jpg'),'.')-1) as fileType;
 

替换函数:replace( str, from_str, to_str)

该函数是多字节安全的,也就是说你不用考虑是中文字符还是英文字符。
str 需要进行替换的字符串
from_str 需要被替换的字符串
to_str 需要替换的字符串

# 将分隔符“,”逗号替换为“”空。
select REPLACE('123,456,789',',','') -- 123456789

特殊替换:
INSERT(str,pos,len,newstr)函数用于在字符串 str 的指定位置 pos 之后插入子串 newstr,替换随后的 len 个字符。

SELECT INSERT('MySQL字符串函数', 6, 3, '日期') AS str; -- MySQL日期函数

在字符串的指定位置插入子字符串

INSERT(original_string, @pos, @len, new_string);

在这里,original_string是我们要在其中一些特定数目的字符的位置插入新字符串的字符串。
@pos是新字符串开始插入的位置。
@len是应从原始字符串中删除的字符数。删除字符的起点是@pos的值。
New_string是我们要插入到原始字符串中的字符串。

结论:
① 当@pos是新字符串开始插入的位置 <= 0 返回源字符串(插了个寂寞)
② 当@pos>0且 @len<0 返回 original_string的@pos位置之前的部分 + new_string
③ 当@pos>0且 @len>0 这才是常规玩法,返回( 返回字符串s1,其子字符串起始于位置x,被字符串s2取代len个字符。)
④ 当@pos>0且 @pos>CHAR_LENGTH(original_string) 返回源字符串(插了个寂寞)

# 这里写个半智能判断的,如果输入的起始长度超过字符串的长度则在字符串末尾添加目标字符串,
# 一个例子根据自己实际需求变动即可
-- 您好,欢迎访问阿阿的博客pan
SELECT INSERT('您好,欢迎访问阿阿的博客',IF(CHAR_LENGTH('您好,欢迎访问阿阿的博
客')>12,12,CHAR_LENGTH('您好,欢迎访问阿阿的博客'))+1,0,'pan');
-- 返回字符串s1,其子字符串起始于位置x,被字符串s2取代len个字符。
-- 输出结果:您好,欢迎访问pan的博客
SELECT INSERT('您好,欢迎访问阿阿的博客',8,2,'pan'); 
-- 您好,欢迎访问阿阿pan的博客
SELECT INSERT('您好,欢迎访问阿阿的博客',10,0,'pan'); 




SELECT CHAR_LENGTH('您好,欢迎访问阿阿的博客'); -- 12
 -- 下面返回都是 您好,欢迎访问阿阿的博客
SELECT INSERT('您好,欢迎访问阿阿的博客',14,0,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',14,-2,'pan');




-- 您好,欢迎访问阿阿的博pan客
SELECT INSERT('您好,欢迎访问阿阿的博客',12,0,'pan');
-- 您好,欢迎访问阿阿的博pan
SELECT INSERT('您好,欢迎访问阿阿的博客',12,-1,'pan');
 -- 您好,欢迎访问阿阿的博pan
SELECT INSERT('您好,欢迎访问阿阿的博客',12,-2,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',12,-6,'pan');
-- 您好,欢迎访问pan
SELECT INSERT('您好,欢迎访问阿阿的博客',8,-2,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',8,-1,'pan');
-- 您好,欢迎访问pan阿阿的博客
SELECT INSERT('您好,欢迎访问阿阿的博客',8,0,'pan');



-- 您pan好,欢迎访问阿阿的博客
SELECT INSERT('您好,欢迎访问阿阿的博客',2,0,'pan');
-- 您pan,欢迎访问阿阿的博客
SELECT INSERT('您好,欢迎访问阿阿的博客',2,1,'pan');
-- 您pan问阿阿的博客
SELECT INSERT('您好,欢迎访问阿阿的博客',2,5,'pan') ;
-- 您pan
SELECT INSERT('您好,欢迎访问阿阿的博客',2,-1,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',2,-2,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',2,-6,'pan');



-- 您好,欢迎访问阿阿的博客
SELECT INSERT('您好,欢迎访问阿阿的博客',-2,0,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',-2,1,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',-2,5,'pan') ;
SELECT INSERT('您好,欢迎访问阿阿的博客',-2,-1,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',-2,-2,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',-2,-6,'pan');



SELECT INSERT('您好,欢迎访问阿阿的博客',0,0,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',0,1,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',0,5,'pan') ;
SELECT INSERT('您好,欢迎访问阿阿的博客',0,-1,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',0,-2,'pan');
SELECT INSERT('您好,欢迎访问阿阿的博客',0,-6,'pan');


 

MYSQL 中的去除空格函数或者过滤指定的字符串:

mysql中的去除左/右空格函数:较于简单不做详细描述
LTRIM(str)、RTRIM(str)

trim函数可以过滤指定的字符串:默认过滤空格
完整格式:TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
BOTH :两端 、 LEADING :前缀 、 TRAILING:后缀、 remstr:过滤条件(不写默认空格)
简化格式:TRIM([remstr FROM] str)

# 默认去除首尾空格
SELECT TRIM(  ' //http://www.baidu.com// '); -- //http://www.baidu.com//
# 去除指定字符串的首尾
SELECT TRIM( '/' FROM '//http://www.baidu.com//');  -- http://www.baidu.com
# 去除指定字符串的前缀、两端、后缀
SELECT TRIM(LEADING '/' FROM '//http://www.baidu.com//'); -- http://www.baidu.com//
SELECT TRIM(BOTH '/' FROM '//http://www.baidu.com//');  -- http://www.baidu.com
SELECT TRIM(TRAILING '/' FROM '//http://www.baidu.com//');  -- //http://www.baidu.com

# 来个综合复杂型的:环境:一个空格后再回车了一下,要删除空格+回车
SELECT  trim(BOTH '\r\n' FROM trim(' 
//http://www.baidu.com//
 ')) as paths  ;  --//http://www.baidu.com//

顺带普及一下MySQL字符串尾部空格匹配潜在的问题
需求:比如库里一个字段 值是: abc 、abc空格 这种形式 ,你只想查出abc不想查出abc空格的数据。 有以下解决办法 推荐使用二进制方法:BINARY

select * from table_name where coulm_name = 'abc' --这个会把含空格也查询出来
解决方法一: 使用like
select * from table_name where coulm_name like 'abc' 

解决方法二: 使用BINARY
select * from table_name where coulm_name =  BINARY('abc') 
select * from table_name where coulm_name =  BINARY 'abc' -- 这个写法也行

解决方法三: 使用 length()函数
select * from table_name where length(coulm_name) =  length('abc')

解决方法四: 业务解决方案(根源上解决)
对于数据的录入,去除尾部空格;

MYSQL 填充字符串函数 LPAD(str,len,padstr),RPAD(str,len,padstr)

第二位参数是最终返回字符串长度的意思 所以大于0才有意义,
当len 小于str的长度时,那么此函数实际上对源串进行截取处理,
当len 大于str的长度时,不够的长度用padstr中的对应长度来填充。
padstr参数: 必填 如果为空字符需要注意

# LPAD、RPAD共性 当第三为参数为空字符时 且len大于str长度时为null  当不写第三位参数报错
SELECT LPAD('hi',2);-- 报错
SELECT LPAD('hi',2,'');-- hi
# 因为你是空串 达不到我的目标长度 返回 null
SELECT RPAD('hi',3,'');-- 空 null  
SELECT LENGTH('');  -- 0
select LENGTH(null) -- null

SELECT LPAD('hi',-1,'??');-- 空 null
SELECT LPAD('hi',0,'??');-- 空字符串
SELECT LPAD('hi',1,'??');-- h
SELECT LPAD('hi',2,'??');-- hi
SELECT LPAD('hi',3,'??');-- ?hi
SELECT LPAD('hi',4,'abc');-- abhi
SELECT LPAD('hi',6,'abc');-- abcahi

SELECT RPAD('hi',-1,'??');-- 空 null
SELECT RPAD('hi',0,'??');-- 空字符串
SELECT RPAD('hi',1,'??');-- h
SELECT RPAD('hi',2,'??');-- hi
SELECT RPAD('hi',3,'??');-- hi?
SELECT RPAD('hi',4,'abc');-- hiab
SELECT RPAD('hi',6,'abc');-- hiabca


当 LPAD(str,len,padstr)中的str为int类型或者padstr为int类型(非char类型)时,此时,使用navicat可以正常执行,但是我遇到在项目中会乱码出错,建议先转为char类型在进行填充。
cast进行类型转换后 问题解决:SELECT RPAD(cast(‘123’ as char),6,‘abc’); – 123abc

MySQL实现下划线转驼峰


# 注意这里是针对单个下划线
select   concat (substring('admin_tesr', 1, instr('admin_tesr', '_' ) - 1),upper(substring('admin_tesr', instr('admin_tesr', '_' )+1,1)),substring('admin_tesr', instr('admin_tesr', '_' )+2,char_length('admin_tesr'))); -- adminTesr  (admin	T	esr) 
#上面针对单个下划线,那多个怎么办
# 最简单就是case when下划线次数 通过不断套娃处理 每次弄到只处理一个下划线的情况
select  substring('admin_tesr_jj', 1, instr('admin_tesr_jj', '_' ) - 1),upper(substring('admin_tesr_jj', instr('admin_tesr_jj', '_' )+1,1)),substring('admin_tesr_jj', instr('admin_tesr_jj', '_' )+2,char_length('admin_tesr_jj')); -- admin	T	esr_jj

函数法:

CREATE FUNCTION `underlineToCamel` (
	paramString VARCHAR ( 200 )) RETURNS VARCHAR ( 200 ) CHARSET utf8 DETERMINISTIC BEGIN
	
	SET paramString = LOWER( paramString );
	
	SET paramString = REPLACE ( paramString, '_a', 'A' );
	
	SET paramString = REPLACE ( paramString, '_b', 'B' );
	
	SET paramString = REPLACE ( paramString, '_c', 'C' );
	
	SET paramString = REPLACE ( paramString, '_d', 'D' );
	
	SET paramString = REPLACE ( paramString, '_e', 'E' );
	
	SET paramString = REPLACE ( paramString, '_f', 'F' );
	
	SET paramString = REPLACE ( paramString, '_g', 'G' );
	
	SET paramString = REPLACE ( paramString, '_h', 'H' );
	
	SET paramString = REPLACE ( paramString, '_i', 'I' );
	
	SET paramString = REPLACE ( paramString, '_j', 'J' );
	
	SET paramString = REPLACE ( paramString, '_k', 'K' );
	
	SET paramString = REPLACE ( paramString, '_l', 'L' );
	
	SET paramString = REPLACE ( paramString, '_m', 'M' );
	
	SET paramString = REPLACE ( paramString, '_n', 'N' );
	
	SET paramString = REPLACE ( paramString, '_o', 'O' );
	
	SET paramString = REPLACE ( paramString, '_p', 'P' );
	
	SET paramString = REPLACE ( paramString, '_q', 'Q' );
	
	SET paramString = REPLACE ( paramString, '_r', 'R' );
	
	SET paramString = REPLACE ( paramString, '_s', 'S' );
	
	SET paramString = REPLACE ( paramString, '_t', 'T' );
	
	SET paramString = REPLACE ( paramString, '_u', 'U' );
	
	SET paramString = REPLACE ( paramString, '_v', 'V' );
	
	SET paramString = REPLACE ( paramString, '_w', 'W' );
	
	SET paramString = REPLACE ( paramString, '_x', 'X' );
	
	SET paramString = REPLACE ( paramString, '_y', 'Y' );
	
	SET paramString = REPLACE ( paramString, '_z', 'Z' );
	
	SET paramString = REPLACE ( paramString, '_', '' );
	RETURN paramString;

END




MySQL 查找某个字符串第n次出现的位置

# MySQL 查找某个字符串第n次出现的位置
SELECT LENGTH(SUBSTRING_INDEX( 'admin_tesr_vv', '_', 2 )) + 1;#11
# 超过出现次数
SELECT LENGTH(SUBSTRING_INDEX( 'admin_tesr_vv', '_', 3)) + 1; #14
SELECT LENGTH(SUBSTRING_INDEX( 'admin_tesr_vv', '_', 4)) + 1; #14
SELECT LENGTH(SUBSTRING_INDEX( 'admin_tesr_vvff_jj_kk_dd', '_', 4)) + 1; #19

MySQL中查找字符串中某个符号或字符出现的次数

# mysql中查找字符串中某个符号或字符出现的次数
SELECT LENGTH('10,A,B')-LENGTH(REPLACE('10,A,B',',','')) '逗号出现的次数'; #2
# 这个严谨点
SELECT char_length('10,A,B')-char_length(REPLACE('10,A,B',',','')) '逗号出现的次数';#2

MYSQL 返回字符串 str 中第一次(最后一次)出现的索引位置及出现次数

注: 你肯定会想如果要获取最后一次出现的位置怎么办 这里给个思想
(可以用REVERSE倒置 LENGTH(str) - LOCATE(REVERSE(str), REVERSE(substr))
这个公式应该还有问题,思路是对的,你可以调整下公式里字符的位置(加减))
指定字符串最后一次出现的位置

# 函数法-------
drop function if exists getIndex;
CREATE FUNCTION getIndex(str varchar(500),spstr varchar(500))
RETURNS int
RETURN length(str) - LOCATE(REVERSE(spstr),REVERSE(str)) + 1 - length(spstr) + 1;
# 下面4个方法 一样的结果
SELECT
(char_length( 'www.ccffftech.com.cec.dd.c4c.cdcf' ) - char_length(
substring_index( 'www.ccffftech.com.cec.dd.c4c.cdcf', '.c',- 1 )) - char_length( '.c' ) + 1) realLength;-- 29
	
	
select 
(char_length('www.ccffftech.com.cec.dd.c4c.cdcf' ) - 
INSTR(REVERSE('www.ccffftech.com.cec.dd.c4c.cdcf'),REVERSE('.c'))  - char_length('.c') + 2) realLength; -- 29
	

select (char_length('www.ccffftech.com.cec.dd.c4c.cdcf') 
- LOCATE(REVERSE('.c'),REVERSE('www.ccffftech.com.cec.dd.c4c.cdcf')) + 1 
- char_length('.c') + 1) realLength ; -- 29


select locate('.c','www.ccffftech.com.cec.dd.c4c.cdcf',char_length('www.ccffftech.com.cec.dd.c4c.cdcf')
 - locate(REVERSE('.c'),REVERSE('www.ccffftech.com.cec.dd.c4c.cdcf')) + 1 - CHAR_LENGTH('.c')) as indexOfNum; -- 29

 

指定字符串出现的次数

-- 获取 索引字符 在整体字符串中出现的次数
drop function if exists getNum;
CREATE FUNCTION getNum(str varchar(500),spstr varchar(500))
RETURNS int
RETURN (length(str) - length(replace(str,spstr,'')))/length(spstr);

select getNum('asdfgasdfadfgeadfdf','adf');-- 2

select CEIL((length('asdfgasdfadfgeadfdf') - length(replace('asdfgasdfadfgeadfdf','adf','')))/length('adf')) -- 2

获取指定字符串出现的索引集合

drop function if exists getIndexList;
CREATE FUNCTION getIndexList(str varchar(500),spstr varchar(500))
RETURNS VARCHAR(500)
RETURN (
select
group_concat(t.n) as list
from
(
select 
(select 
case when 
(length(str)-LOCATE(REVERSE(spstr),REVERSE(str)) + 1) < (length(SUBSTRING_INDEX(str,spstr,help_topic_id+1))+1)
THEN -1 ELSE (length(SUBSTRING_INDEX(str,spstr,help_topic_id+1))+1) end) as n
from mysql.help_topic where help_topic_id < (length(str) - length(replace(str,spstr,'')))/length(spstr)
) t 
);
select getIndexList('abfffabffabffabab','ff');


select
group_concat(t.n) as list
from
(
select 
(select 
case when 
(length('abfffabffabffabab')-LOCATE(REVERSE('ff'),REVERSE('abfffabffabffabab')) + 1) < (length(SUBSTRING_INDEX('abfffabffabffabab','ff',help_topic_id+1))+1)
THEN -1 ELSE (length(SUBSTRING_INDEX('abfffabffabffabab','ff',help_topic_id+1))+1) end) as n
from mysql.help_topic where help_topic_id < (length('abfffabffabffabab') - length(replace('abfffabffabffabab','ff','')))/length('ff')
) t ;



返回目标字符串第n次出现时位于整体字符串的位置
注:当次数大于最后一次出现位置返回 -1

-- 获取字符串在整体字符串中第n次出现的位置 如果超过最大出现位置返回 -1
drop function if exists indexOfNum;
CREATE FUNCTION indexOfNum(str varchar(500),spstr varchar(500), num int )
RETURNS int
RETURN (case when 
(length(str)-LOCATE(REVERSE(spstr),REVERSE(str)) + 1) < (length(SUBSTRING_INDEX(str,spstr,num))+1)
THEN -1 ELSE (length(SUBSTRING_INDEX(str,spstr,num))+1) end);
select indexOfNum('abfffabffabffabab','a',4); 
# 参数3个 待处理字符串:abfffabffabffabab 目标字符串:a 出现次数:4
select 
case when 
(length('abfffabffabffabab')-LOCATE(REVERSE('a'),REVERSE('abfffabffabffabab')) + 1) < (length(SUBSTRING_INDEX('abfffabffabffabab','a',4))+1)
THEN -1 ELSE (length(SUBSTRING_INDEX('abfffabffabffabab','a',4))+1) end as indexOfNum


INSTR(str,substr)函数
INSTR(str,substr)函数用于返回子串 substr 在字符串 str 中第一次出现的索引位置,
没有找到子串时返回 0。没有返回负数的情况。
此函数是多字节安全的,并且仅当至少一个参数为二进制字符串时才区分大小写

select INSTR('MySQL字符串函数', '字符串') AS index1, -- 6
       INSTR('MySQL字符串函数', '日期') AS index2, -- 0
       INSTR('MySQL字符串函数', '') AS index3, -- 1
       INSTR('MySQL字符串函数', null) AS index4; --空 null

还可以这么玩:替换、代替like、作为where条件、配合截取字符串使用,配合order by使用、配合IN排序

INSERT(str,pos,len,newstr)函数用于在字符串 str 的指定位置 pos 之后插入子串 newstr,替换随后的 len 个字符。

SELECT INSERT('MySQL字符串函数', 6, 3, '日期') AS str; -- MySQL日期函数

在mysql中使用内部函数instr,可代替传统的like方式查询,并且速度更快。

# 传统的方法是:like			
select name from 用户表 where name like '%军%';
# instr 函数法  下面两个一样
select name from 用户表 where instr(name,'军');
select name from 用户表 where instr(name,'军')>0;

-- locate、position 和 instr 的差別只是参数的位置不同,
-- 同时locate 多一个请始位置的参数外,两者是一样的。

SELECT `column` FROM `table` where `condition` like `%keyword%’

-- 事实上,可以使用 locate 和 instr 这两个函数来代替

SELECT `column` from `table` where locate(‘keyword’, `condition`)>0
或是 locate 的別名 position
SELECT `column` from `table` where position(‘keyword’ IN `condition`)
或是
SELECT `column` from `table` where instr(`condition`, ‘keyword’ )>0
-- 速度上这三个比用 like 稍快了一點。

联合SUBSTR来截取邮箱@之前的字符串

SELECT SUBSTR(email,1,INSTR(email,'@')-1) 用户名 FROM stuinfo;

利用INSTR来实现特殊的排序

SELECT * FROM tblTopic ORDER BY INSTR( topicTitle, 'ha' ) > 0 DESC

LOCATE(substr,str)函数
这个函数支持多字节字元,并且只有当至少有一个参数是二进制字符串时区分大小写。
注:当在 MySQL 4.0 中时,如有任一参数是一个二进制字符串,它才是字母大小写敏感的
LOCATE(substr,str)函数也可以用于返回子串 substr 在字符串 str 中第一次出现的索引位置,和 INSTR(str,substr) 函数唯一的不同就是参数的顺序相反。

select LOCATE('字符串','MySQL字符串函数') AS index1, -- 6
       LOCATE( '日期','MySQL字符串函数') AS index2, -- 0
       LOCATE('','MySQL字符串函数') AS index3, -- 1
       LOCATE(null,'MySQL字符串函数') AS index4; -- 空 null

当然也可以用来判断字符串(string)中是否包含另一个字符串(subStr)
LOCATE(substr,str,pos)函数 返回子串 substr 在字符串 str 中从位置 pos 开始第一次出现的索引位置
注:pos必须大于第一次出现的位置,才能显示第二次出现的位置

 SELECT LOCATE('S','MySQL Server', 5) AS ind; -- 7

POSITION() 函数: 返回字符串中第一次出现的子字符串的位置。
POSITION(substring IN string)
如果在原始字符串中找不到子字符串,则此函数返回0。
此函数执行不区分大小写的搜索。
注:LOCATE()函数等于position()函数。

SELECT POSITION('COM' IN 'begtut.com') AS MatchPosition; -- 8 

FIELD(str,str1,str2,str3,…) 函数 返回字符串 str 在后续字符串列表中第一次出现的位置,没有找到时返回 0。

返回指定字符串位置的函数FIELD(S,S1,S2,…)

FIELD(S,S1,S2,…)返回字符串s在列表s1,s2,…中第一次出现的位置,在找不到s的情况下,返回值为0

如果s为NULL,则返回值为0,原因是NULL不能同任何值进行同等比较。

SELECT FIELD('李四', '张三', '李四', '王五', '李四') AS ind; -- 2

# 函数中字符串hi出现在列表的第3个字符串位置,因此返回结果为3
# hi != hihi 返回0,hi != hey 返回0,hi = hi 返回3,hi != bas返回0,
# 最后返回的就是非0,除非全部没找到都是0则最终返回0
SELECT FIELD('hi','hihi','hey','hi','bas'); -- 0,0,3,0  --3 
# 列表中没有字符串hi,因此返回结果为0
SELECT FIELD('hi','hihi','hey','hi0','bas'); -- 0,0,0,0  --0
-- 如果s为NULL,则返回值为0,原因是NULL不能同任何值进行同等比较。
SELECT FIELD(null,'hihi','hey',null,'bas'); -- 0
# 下面这种情况实际不会出现,同一个值你写两次那是你写法有毛病,
# 但是练习中可以模拟这种情况,它的结果就是以第一次出现的位置为准
SELECT FIELD('hi','hihi','hey','hi','hi'); -- 0,0,3  --3 
SELECT FIELD('hi','hi','hey','hi','hi'); -- 0,1  --1 

FIND_IN_SET(str,strlist) 函数 返回字符串 str 在列表字符串 strlist 中首次出现的位置,strlist 由 N 个子串使用逗号分隔组成。
这个函数说白了就是处理字段中的数据集合,常搭配in 、like使用
FIND_IN_SET(str,strlist)

str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (1,2,6,8,10,22)
查询字段(strlist)中包含(str)的结果,返回结果为null或记录

SELECT FIND_IN_SET('李四', '张三,李四,王五,李四') AS ind; -- 2

SELECT FIND_IN_SET('b', 'a,b,c,d'); -- 2
SELECT FIND_IN_SET('b', 'bc'); -- 0
SELECT FIND_IN_SET('b', 'db,b'); -- 2
SELECT FIND_IN_SET('c', 'b,'); -- 0

find_in_set()和in的区别:
在mysql中in可以包括指定的数字,而find_in_set()用于特定的数据类型。

#两者等价
SELECT * from table_name where FIND_IN_SET(EKGRP,'C54,C02,C14,C60') -- 这是变量
SELECT * from table_name where EKGRP in ('C54','C02','C14','C60'') -- in里面是常量
# 下面这个肯定就不行了
SELECT * from table_name where EKGRP in ('C54,C02,C14,C60') -- 不能in变量
常量,则可以直接用IN, 否则要用find_in_set()函数。

find_in_set()和like的区别:
主要的区别就是like是广泛的模糊查询,而 find_in_set() 是精确匹配,并且字段值之间用‘,'分开。

# 如查询角色集合中有2的
SELECT userid, userrole 角色 FROM `user` 
userid|角色 |
------+------+
1|1,2,23,15 |
2|1,23,15 |
SELECT userid ,userrole 角色 FROM `user` WHERE userrole LIKE '%2%'; -- 有脏数据
userid|角色 |
------+------+
1|1,2,23,15 |
2|1,23,15 |
SELECT userid ,userrole 角色 FROM `user` WHERE find_in_set('2',userrole) -- 精确的数据
userid|角色 |
------+------+
1|1,2,23,15 |

MYSQL 将字符串 str 中的字符顺序进行反转。

SELECT REVERSE('123456');	 -- 654321		 
SELECT REVERSE('123456')='654321' AS str; -- 1

MYSQL SPACE(N) 返回n个空格

返回由N个空格字符组成的一个字符串。(返回n个空格)

# a b直接增加5个空格
select CONCAT('a',SPACE(5),'b')

MYSQL 满足对应条件返回对应值 INTERVAL()、ELT()、FIELD()

ELT(N,str1,str2,str3,…)
ELT()函数是分值函数,功能有点类似很多编程语言中的switch关键字。
如果N= 1,返回str1,如果N= 2,返回str2,等等。如果N小于1或大于参数个数,返回NULL。ELT()是FIELD()反运算。

# 如果N =1返回str1,如果N= 2返回str2,等等。
# 返回NULL:如果参数的数量小于1或大于N。ELT()是FIELD()的补集。
SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo'); -- ej
SELECT ELT(0, 'ej', 'Heja', 'hej', 'foo'); -- null
SELECT ELT(5, 'ej', 'Heja', 'hej', 'foo'); -- null


FIELD(str,str1,str2,str3,…)
返回str在str1, str2, str3, …清单的索引。如果str没找到,返回0。 FIELD()是ELT()反运算。
参数:
此方法接受以下两个参数:
value:要搜索的指定值。
val1,val2,val3,…:指定要搜索的值列表。
返回值:
它返回给定值列表中指定值第一次出现的索引位置。
因此可搭配 order by 排序使用

SELECT FIELD("5", "2", "5", "5", "0"); -- 2
SELECT FIELD('halo','hello','halo','test','world'); -- 2
SELECT FIELD('o','hello','halo','test','world'); -- 0
SELECT FIELD('','hello','halo','test','world'); -- 0
SELECT FIELD(null,'hello','halo','test','world'); -- 0

ELT()与FIELD()函数联合使用:
此函数联合使用可以将原本内容按照新的格式重命名

SELECT value,  ELT( FIELD(value,'str1','str2','str3'), 'new1','new2','new3')  FROM table ;

value|newvalue|
------+------+  
str1   |     new1|
str2   |     new2|
str3   |     new3|


SELECT id,elt(
		field(
		model_event,
		'1301',
		'1302',
		'1303',
		'1304',
		'1305',
		'1306'
		),
		'改款',
		'换代',
		'年型',
		'上市',
		'特别版',
		'新增'
	) AS mge FROM table_name;
	

在Oracle中我们可以用decode()函数 来获取到来源名称:decode(条件,值1,返回值1,值2,返回值2…,缺省值);
 在Mysql中呢,我们需要ELT()、FIELD()来实现 ,必要的时候还会用到IFNULL()来实现最终效果。对于上述需求,我们可以采取以下sql来实现:

SELECT *,IFNULL(ELT(FIELD(type,'1','2','3','4'),'来源A','来源B','来源C','来源D'),'未知')
 name from order;

而Mysql中decode的函数是用来加密使用的,只有两个参数,用于解码编码的字符串并返回原始字符串。与encode是一对

select ENCODE('passwd','123456') as '加密',
DECODE(ENCODE('passwd','123456'),'123456') as '解密'

INTERVAL()函数
INTERVAL()函数可以返回分段后的结果,语法如下:
INTERVAL(N,N1,N2,N3,…)
其中,N是要判断的数值,N1,N2,N3,…是分段的间隔。
这个函数的返回值是段的位置:
如果N<N1,则返回0,
如果N1<=N<N2,则返回1,
如果N2<=N<N3,则返回2。
所以,区间是前闭后开的。

# percent字段参与判断,设定的区段是25,50,
# 那么小于25的值返回0,大于等于25小于50的值返回1,大于等于50的值返回2。
SELECT id,percent,INTERVAL(percent,25,50) from test;

还可以把INTERVAL()函数用在GROUP BY中:

SELECT INTERVAL (percent, 0, 26, 51),   COUNT(1)
FROM  test
GROUP BY INTERVAL (percent, 0, 26, 51);

INTERVAL()函数和ELT()函数合用
因为两个函数都是按照整数位置运作的,所以二者可以合用。

SELECT id,percent,ELT(INTERVAL(percent,0,26,51),'0-25','26-50','>50') result from test;

MYSQL COALESCE(value,…)

返回值为列表当中的第一个非 NULL值,在没有非NULL 值得情况下返回值为 NULL。

在这里空字符不是空 下面3个都返回本身的空
select COALESCE('','');
select COALESCE('','','2');
select COALESCE('',null,'2');
# 第一个是null 才会找后面第一次不是 null的那条数据
select COALESCE(null,'');
select COALESCE(null,'','2');
select COALESCE(null,null,'2'); -- 2
select COALESCE(null,null,null); -- null

select COALESCE('1','','2'); -- 1

MYSQL 返回参数列表的最大/小值

只要参数expr有一个为NULL,都会返回NULL。
注意比较时,要类型一致才可以比较。

SELECT GREATEST(3, 12, 34, 8, 25); -- 34
SELECT GREATEST("w3Schools.com", "microsoft.com", "apple.com"); -- w3Schools.com

SELECT LEAST(3, 12, 34, 8, 25); -- 3
SELECT LEAST("w3Schools.com", "microsoft.com", "apple.com"); -- apple.com
# 返回最大日期
SELECT GREATEST('2021-12-31','2022-01-31')

# 部分为数值型,但是字符串可以根据expr_1的数据类型通过隐式类型转换转成数值型:
SELECT GREATEST(2, '5', 12, 3, 16, 8, 9)  -- 16
SELECT GREATEST(6, '22', 12, 16, 8, 9)  -- 22
# 部分为字符型,会把非字符型转换成字符型
SELECT GREATEST('A', 6, 7, 5000, 'E', 'F','G') -- 5000   好像会忽略非数字
# 全部为字符型,首字母相等:
 SELECT GREATEST('A', 'B', 'C', 'D', 'E','GA', 'GAB') -- GAB
# 部分为数值型,但是字符串不能通过隐式类型转换成数值 可以认为弄成0了 
SELECT GREATEST(2, 'A', 12, 3, 16, 8, 9) -- 16    好像会忽略非数字

SELECT LEAST(6, '2', 12, 3, 16, 8, 9)  -- 2   
SELECT LEAST(2, '5', 12, 3, 16, 8, 9)  -- 2
SELECT LEAST('A', 6, 7, 5000, 'E', 'F','G') -- 0
SELECT LEAST('A', 'B', 'C', 'D', 'E','GA', 'GAB') -- A
SELECT LEAST(2, 'a', 12, 3, 16, 8, 9) -- 0
# GREATEST中数字、非数字组合会挑选 数字最大的 LEAST则直接为0

返回字段最值

SELECT 
    company_id,
    LEAST(IFNULL(q1, 0),
            IFNULL(q2, 0),
            IFNULL(q3, 0),
            IFNULL(q4, 0)) low,
    GREATEST(IFNULL(q1, 0),
            IFNULL(q2, 0),
            IFNULL(q3, 0),
            IFNULL(q4, 0)) high
FROM
    revenues;

mysql中函数greatest 与MAX区别 横向、纵向求最大
greatest (a,b,c,d,d) 、max(a)
这样就能看明白了,greatest 求的是某几列的最大值,横向求最大(一行记录)
max(a) 一看就明白了,是给纵向求最大(多行记录)。

MYSQL json 使用 类型 查询 函数

这篇博文写的很详细这块可参考此网址

MYSQL 获取 ASCII码值

ASCII(str):返回字符串str的最左面字符的ASCII代码值。
如果str是空字符串,返回0。如果str是NULL,返回NULL。

select ASCII(''); -- 0
select ASCII(null); -- null
select ASCII('2'); -- 50
select ASCII('25'); -- 50
select ASCII(2); -- 50
select ASCII('adxa'); -- 97
SELECT ASCII('简'); -- 231 
SELECT ORD('简'); -- 15183488

应用:作为条件使用

# 在where语句中使用ASCII函数 输出aut_name首字母的ASCII值小于70的数据
SELECT aut_name,ASCII(aut_name)as n FROM author WHERE ASCII(aut_name)<70;
# 输出字段中不存在没有ASCII值的数据
SELECT * FROM table_name WHERE NOT column_to_check REGEXP '[A-Za-z0-9.,-]';
# 与SUBSTRING一起使用计算字符串第二个以后的ASCII值
select ASCII(SUBSTRING('database',2,1));

ORD()函数
ORD() 函数返回字符串第一个字符的ASCII 值。
如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code…]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。

select ORD(''); -- 0
select ORD(null); -- null
select ORD('2'); -- 50
select ORD('25'); -- 50
select ORD(2); -- 50
select ORD('adxa'); -- 97
SELECT ASCII('简'); -- 231 
SELECT ORD('简'); -- 15183488

CONV(N,from_base,to_base)
在不同的数字基之间变换数字。返回数字N的字符串数字,从from_base基变换为to_base基,如果任何参数是NULL,返回NULL。 参数N解释为一个整数,但是可以指定为一个整数或一个字符串。最小基是2且最大的基是36。 如果to_base是一个负数,N被认为是一个有符号数,否则,N被当作无符号数。 CONV以64位点精度工作。
下面大白话说法:
这个函数是用来将字符转换进制的,例如将a转成ASCII码(换个说法就是将16进制的a换成10进制)
那就直接用conv(hex(‘a’),16,10)
清晰明了,第一个参数是要转换的字符,第二个就是要转换的字符的进制,第三个参数就是转换成的进制
就是说第二个参数要和第一个参数的类型一致,第三个参数就是想要得到的进制

select conv(100,10,2); -- 1100100
select conv(100,10,8); -- 144
select conv(100,10,16); -- 64

HEX(N)
返回十六进制值N一个字符串的表示, 在此N是一个长整型(BIGINT)数字,这等价于CONV(N,10,16)。如果N是NULL,返回NULL。

select HEX(255); -- FF

select CONV(255,10,16); -- FF

BIN(N)
返回二进制值N的一个字符串表示, 在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2)。如果N是NULL,返回NULL
OCT(N)
返回八进制值N的一个字符串的表示, 在此N是一个长整型数字,这等价于CONV(N,10,8)。如果N是NULL,返回NULL。

CHAR(N,…)
CHAR()将参数解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串。NULL值被跳过。

select CHAR(77); -- 'M'

select CHAR(77,121,83,81,'76'); -- 'MySQL'
 
select CHAR(77,77.3,'77.3'); -- 'MMM'

非二进制字符串数据:CHAR,VARCHAR,TEXT

https://blog.csdn.net/CodeMyDream/article/details/51548670

MySQL字符串和二进制

数据类型
CHAR——用于表示固定长度的字符串
VARCHAR——用于表示可变长度的字符串
BINARY——用于表示固定长度的二进制数据
VARBINARY——用于表示可变长度的二进制数据
BOLB——用于表示二进制大数据
TEXT——用于表示大文本数据
ENUM——表示枚举类型,只能存储一个枚举字符串值
SET——表示字符串对象,可以有零或多个值
BIT——表示位字段类型

https://www.itheima.com/news/20191121/161529.html
https://blog.csdn.net/newchitu/article/details/84984605
在这里插入图片描述

补充知识

扩展一下,MySQL5.0.3版本之后varchar类型的变化。

1)、MySQL 5.0.3 之前:0–255字节,如:varchar(20)中的20表示字节数,如果存放utf-8编码的话只能放6个汉字。varchar(n),这里的n表示字节数。
    MySQL 5.0.3 之后:0–65535字节,varchar(20)表示字符数,不管什么编码,既汉字也能放20个。但最多占65532字节(两个字节存放长度,小于255字节用1个字节存放长度),varchar(n)这里的n表示字符数,比如varchar(200),不管是英文还是中文都可以存放200个。其他具体区别,可自行网上查询。

MySql常用函数大全讲解:https://blog.csdn.net/sinat_38899493/article/details/78710482

https://wenku.baidu.com/tfview/59a72482846a561252d380eb6294dd88d0d23dba.html?fr=launch_ad&SS-bdtg02&utm_source=bdss-WD&utm_medium=cpc&utm_account=SS-bdtg02&e_creative=54763508728&e_keywordid=339396333750&bd_vid=8311196720406680861

https://blog.csdn.net/weixin_34221599/article/details/113416294?spm=1001.2101.3001.6650.13&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-13.queryctrv4&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-13.queryctrv4&utm_relevant_index=17

MySQL面试题-数据类型:https://blog.csdn.net/qq_44545083/article/details/105200819?spm=1001.2101.3001.6650.12&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-12.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7ERate-12.pc_relevant_default&utm_relevant_index=16

MySQL 8.0窗口函数:https://www.cnblogs.com/DataArt/p/9961676.html

https://blog.csdn.net/weixin_42467088/article/details/113627050?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1.pc_relevant_paycolumn_v3&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1.pc_relevant_paycolumn_v3&utm_relevant_index=1

MySQL-locate()函数:https://blog.csdn.net/m0_37524661/article/details/87859423

MySQL中REGEXP正则表达式使用大全


# 询xxxyyy字符串中是否以xx开头,结果值为1,表示值为true,满足条件。
select 'xxxyyy' regexp '^xx';  
# 查找用户表中Email格式错误的用户记录
SELECT * FROM users WHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$'
# 查询找到所有的名字以'ok'结尾
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';  
# 查询找到所有的名字包函'mar'的字符串
SELECT name FROM person_tbl WHERE name REGEXP 'mar';  
# 查询找到所有名称以元音开始和'ok'结束 的
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

mysql无符号和有符号的区别

mysql无符号和有符号的区别
无符号unsigned 表示设置的的数据为0或者正数 非负数;
有符号则可以是负数 -;
内存占比 有符号 0-255 无符号 -127~127

MAKE_SET

《MySQL数据库锁相关技巧汇总》
《MySQL事务操作技巧汇总》
《MySQL日志操作技巧大全》
《MySQL存储过程技巧大全》
《MySQL常用函数汇总》

MYSQL SOUNDEX()函数

是一个将任何文本串转换为描述语音表示的字母模式的算法。
他考虑了类似于发音字符和字节,能够对文本发音进行比较而不是字母比较。

返回str的一个同音字符串。听起来“大致相同”的2个字符串应该有相同的同音字符串。一个“标准”的同音字符串长是4个字符,但是SOUNDEX()函数返回一个任意长的字符串。你可以在结果上使用SUBSTRING()得到一个“标准”的 同音串。所有非数字字母字符在给定的字符串中被忽略。所有在A-Z之外的字符国际字母被当作元音。

# 如果库中存在一名名为Y.LEE的客户而搜索的时候的输入错误,下面的sql是不会有任何返回结果的。
SELECT CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_NAME = 'Y LEE'
# 因为两者发音相似,所以他们的SOUNDEX值匹配,这样就会返回一条数据。
SELECT CUSTOMER_NAME FROM CUSTOMER WHERE SOUNDEX(CUSTOMER_NAME) =SOUNDEX('Y LEE')

SELECT SOUNDEX('Hello');-- H400  

MySQL 中实现提取字符串中的数字的自定义函数分享


CREATE FUNCTION GetNum (Varstring varchar(50))
RETURNS varchar(30)
BEGIN
DECLARE v_length INT DEFAULT 0;
DECLARE v_Tmp varchar(50) default '';
set v_length=CHAR_LENGTH(Varstring);
WHILE v_length > 0 DO
IF (ASCII(mid(Varstring,v_length,1))>47 and ASCII(mid(Varstring,v_length,1))<58 )   THEN
set v_Tmp=concat(v_Tmp,mid(Varstring,v_length,1));
END IF;
SET v_length = v_length - 1;
END WHILE;
RETURN REVERSE(v_Tmp);
END;
select GetNum("d5ff44dfd666"); -- 544666

MYSQL 提取字符串中的数字

它们都无法提取到0开头的数字,从非0起步开始
第一种 他能拿到整个串中的非0开头的所有数字
第二种只能提取到字符串前面的非0开头的数字,有局限性
在这里插入图片描述


select id,cast(group_concat(c order by pos separator '') as unsigned)
         as mixed1
  from (
select v.id , v.str, iter.pos,substr(v.str,iter.pos,1) as c
  from test1 v,
       ( select id pos from t10 ) iter
where iter.pos <= length(v.str)
and   ascii(substr(v.str,iter.pos,1)) between 48 and 57
      ) y
group by id,str
order by 1


select CAST('21岁' as SIGNED); -- 21
                              
select  CONVERT('021岁', SIGNED) -- 21

MySQL QUOTE()经常用在防止sql注入

https://blog.csdn.net/yqq__00/article/details/52525732
quote(str)

select REPLACE(REPLACE(REPLACE(QUOTE('方法的'), '%', '\%'), '_', '\_'), ' ', '%'); -- '方法的'

select  INSERT(INSERT('方法的', CHAR_LENGTH('方法的'), 0, '%'), 2, 0, '%'); -- 方%法%的

select length(quote('123')); -- 5 
# 用反斜杠转义str中的单引号----------‘abc’
SELECT QUOTE('abc') ; -- 'abc'

MySQL COMPRESS(string_to_compress)

压缩一个字符串。返回的值是二进制字符串。
COMPRESS()函数将非空字符串存储为未压缩字符串的four-byte长度,然后是压缩字符串。如果字符串以空格结尾,则将“.”字符添加到字符串。另外,应注意,空字符串存储为空字符串。 COMPRESS()函数接受一个参数,该参数是要压缩的字符串。

非空字符串未压缩字符串的四字节长度进行存储(首先为低字节),后面是压缩字符串。如果字符串以空格结尾,就会在后加一个"."号,以防止当结果值是存储在CHAR或VARCHAR类型的字段列时,出现自动把结尾空格去掉的现象。(不推荐使用 CHAR 或VARCHAR 来存储压缩字符串。最好使用一个 BLOB 列代替)。

# 在字符串上实现COMPRESS函数。
SELECT COMPRESS('geeskforgeeks'); -- 
# 在包含字符和整数的字符串上实现COMPRESS函数。
SELECT COMPRESS('geeskforgeeks123');-- \0\0\0x?KOM-?N?/JOM?.642\06??   
# 在字符串上实现COMPRESS函数,并在压缩后返回字符串的长度。
SELECT COMPRESS('geeksforgeeks'), LENGTH(COMPRESS('geeksforgeeks'));-- \0\0\0x?KOM?.N?/J?\0%?f 22
# 在NULL字符串上实现COMPRESS函数,并在压缩后返回字符串的长度。
SELECT COMPRESS(NULL), LENGTH(COMPRESS(NULL)); --  null  null 


SELECT LENGTH(COMPRESS(REPEAT('a',1000)));-- 21
SELECT LENGTH(COMPRESS(''));-- 0
SELECT LENGTH(COMPRESS('a'));-- 13
SELECT LENGTH(COMPRESS(REPEAT('a',16)));-- 15

MySQL 加密/压缩函数

https://blog.csdn.net/weixin_42389421/article/details/113542583

视频解析
在这里插入图片描述

在这里插入图片描述

46.UNCOMPRESS(string_to_uncompress)
对经COMPRESS()函数压缩后的字符串进行解压缩。

47.UNCOMPRESSED_LENGTH(compressed_string)
返回压缩字符串压缩前的长度。

48.UNHEX(str)
执行从HEX(str)的反向操作。就是说,它将参数中的每一对十六进制数字理解为一个数字,并将其转化为该数字代表的字符。
结果字符以二进制字符串的形式返回。

MySQL函数之STRCMP()

STRCMP(expr1,expr2)
若所有的字符串均相同,则返回0,若根据当前分类次序,第一个参数小于第二个,则返回 -1,其它情况返回 1 。

在执行比较时,STRCMP() 使用当前字符集。这使得默认的比较区分大小写, 当操作数中的一个或两个都是二进制字符串时除外。
经过测试貌似没有区分大小写,上面应该是说反了,通过cast函数转二进制后反而区分大小写了,不是一个二进制

SELECT IF(STRCMP(‘test’,‘test’),‘no’,‘yes’); – yes
#MYSQL if中除了0是false 其他都是true

SELECT STRCMP('one', 'oneTwo'); -- -1
SELECT STRCMP('oneTwo', 'one'); -- 1
SELECT STRCMP('oneTwo', 'oneTwo'); -- 0
SELECT STRCMP('A', 'a'); -- 0
SELECT STRCMP('a', 'A'); -- 0
# 有一个为null就是null  空串跟空格一样
SELECT STRCMP('', ''); -- -0
SELECT STRCMP(' ', ''); -- -0
SELECT STRCMP('', ' '); -- -0
SELECT STRCMP(' ', '    '); -- -0
SELECT STRCMP(null, ''); --  null
SELECT STRCMP('g', null); --  null
SELECT STRCMP(null, null); --  null

 SELECT STRCMP('text', 'text2'); # -1
 SELECT STRCMP('text2', 'text'); # 1
 SELECT STRCMP('AbC', 'aBc'); -- 0

SELECT IF(STRCMP('test','test1'),'no','yes'); -- no
SELECT IF(STRCMP('test2','test1'),'no','yes'); -- no
SELECT IF(STRCMP('test','test'),'no','yes'); -- yes

#MYSQL if中除了0是false 其他都是true

SELECT IF(0,'no','yes'); -- yes
SELECT IF(1,'no','yes'); -- yes
SELECT IF(2,'no','yes'); -- yes
SELECT IF('a','no','yes'); -- yes

 SELECT STRCMP('text', 'text2'); # -1
 SELECT STRCMP('text2', 'text'); # 1
 SELECT STRCMP('AbC', 'aBc'); -- 0
 
SELECT STRCMP(cast('A' as BINARY), cast('a' as BINARY)); -- -1

MYSQL保留指定位数函数

这块保留位数函数就3个:FOMRAT、ROUND、TRUNCATE,这里提一下,下面有详细的介绍:

-- 第三个参数可以不写 默认en_US 不认其他形式
SELECT FORMAT(27112020.1052, 2, 'de_DE') -- 27.112.020,11
SELECT FORMAT(27112020.1052, 2, 'en_US') -- 27,112,020.11
SELECT FORMAT(27112020.1052, 2) -- 27,112,020.11
SELECT FORMAT(27112020.1052, 2,'-') -- 27,112,020.11
SELECT FORMAT(130919999.456, 0) -- 130,919,999

select round(1123,2);-- 1123  缺陷整数时取不到小数点后两位
select REPLACE(FORMAT(1123,2),',',''); -- 1123.00 通过FORMAT解决
select round(1123 + 0.0,2);-- 1123.00  通过+0.0解决
select round(CAST(1123 as DECIMAL(10,2)) ,2); -- 1123.00 通过CAST或CONVERT函数解决

SELECT TRUNCATE(4545.1366,3);-- 4545.136  直接截取

MySQL 关于数字列取数逻辑函数

FOMRAT(N,D,locale);函数
FORMAT函数将数字N格式化为格式,如"#,###,###.##",舍入到D位小数。它返回一个值作为字符串。
FOMRAT(N,D,locale);函数接受三个参数:
N是要格式化的数字。
D是要舍入的小数位数。
locale是一个可选参数,用于确定千个分隔符和分隔符之间的分组。如果省略locale操作符,MySQL将默认使用en_US。
该函数格式化给定的数字,将其四舍五入到某个小数位,然后以字符串形式返回该数字。

 SELECT FORMAT(0, 2); -- 0.00
 SELECT FORMAT(14500.2088, 2); -- 14,500.21
 SELECT REPLACE(FORMAT(14500.2088, 2),',',''); -- 14500.21
 # 以下语句使用de_DE语言环境而不是en_US语言环境:
 # de_DE语言环境使用点(.)来分隔千位和逗号(,)来分隔小数点。
 SELECT FORMAT(12500.2015, 2,'de_DE'); -- 12.500,20
 SELECT FORMAT(12505660.208715, 2,'de_DE'); -- 12.500,20

请注意,FORMAT函数返回一个字符串值。这意味着
如果要使用ORDER BY子句对FORMAT函数的结果进行排序,
MySQL将使用基于字符串而非基于数字的方式对结果进行排序。

round():四舍五入函数

round(数值,参数):如果参数的值为正数,表示四舍五入的保留几位小数,
如果参数的值为0,则只保留四舍五入的正数部分
如果参数的值为负数,表示对小数点前第几位进行四舍五入。
select round(1123 + 0.0,2);-- 1123.00 通过+0.0解决这样就会得到想要的保留位数,
当然你也可以通过CAST或CONVERT函数来达到目的:
select round(CAST(1123 as DECIMAL(10,3)) ,3);

select round(1123.26723,2);-- 1123.27
select round(1123.20023,2);-- 1123.20
select round(1123.00023,2);-- 1123.00
select round(1123,2);-- 1123  缺陷整数时取不到小数点后两位
select round(1123 + 0.0,2);-- 1123.00  通过+0.0解决
select round(1123.0,2);-- 1123.00
select round(1123.00,2);-- 1123.00
select round(1123.000,2);-- 1123.00
select round(1123.5950,2);-- 1123.60

select round(1123.26723,1);--  1123.3
select round(1123.26723,0);-- 1123
select round(1123.56723,0);-- 1124

select round(1123.26723,-1); --  1120
select round(1129.26723,-1); --  1130

select round(1123.26723,-2);-- 1100
select round(1153.26723,-2);-- 1200

select round(1123.66723);-- 1124
select round(1123.66723,-4);-- 0 超过最大整数长度 为0

 select round(1123 ,3);-- 1123  
 select round(CAST(1123 as DECIMAL(10,3)) ,3);-- 1123.000

TRUNCATE()函数介绍
TRUNCATE(X,D) 是MySQL自带的一个系统函数。
其中,X是数值,D是保留小数的位数。
其作用就是 按照小数位数,进行数值截取 (此处的截取是按保留位数直接进行截取, 没有四舍五入 )。

2、数值保留规则
规则如下:
1)当 D 大于0,是对数值 X 的小数位数进行操作;

2)当 D 等于0,是将数值 X 的小数部分去除,只保留整数部分;

3)当 D 小于0,是将数值 X 的小数部分去除,并将整数部分按照 D 指定位数,用 0 替换。

SELECT TRUNCATE(123, 3);   # 123
SELECT TRUNCATE(123+0.0 , 3);   # 123.456

SELECT TRUNCATE(123.4567, 3);   # 123.456
SELECT TRUNCATE(123.4567, 2);   # 123.45
SELECT TRUNCATE(123.4567, 1);   # 123.4
SELECT TRUNCATE(123.4567, 0);   # 123
SELECT TRUNCATE(123.4567, -1);  # 120
SELECT TRUNCATE(123.4567, -2);  # 100
SELECT TRUNCATE(199.4567, -2);  # 100
SELECT TRUNCATE(123.4567, -3);  # 0

SELECT TRUNCATE(-123.4567, 3);   # -123.456
SELECT TRUNCATE(-123.4567, 2);   # -123.45
SELECT TRUNCATE(-123.4567, 1);   # -123.4
SELECT TRUNCATE(-123.4567, 0);   # -123
SELECT TRUNCATE(-123.4567, -1);  # -120
SELECT TRUNCATE(-123.4567, -2);  # -100
SELECT TRUNCATE(-123.4567, -3);  # 0

ceil(数值):向上取整 只要整数后的小数点不全为0 结果都为整数部分+1

select ceil(10); -- 10
select ceil(10.00); -- 10
select ceil(10.99); -- 11
select ceil(10.01); -- 11 
select ceil(0.01); -- 1

floor(数值):向下取整 其实就是舍弃小数位 获取整数位

select floor(10); -- 10
select floor(10.00); -- 10
select floor(10.99); -- 11
select floor(10.01); -- 11 
select floor(0.01); -- 1

mod(参数1,参数2):求出余数

select mod(10,2); -- 0
select mod(10,4); -- 2

sqrt(数值):求出平方根

select sqrt(9); -- 3
select sqrt(8); -- 2.8284271247461903

power(底数,指数):返回某数的乘幂

select power(10, 2);  -- 100   10的平方
Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐