mysql正则函数_mysql正则表达式-函数.md

```sql

#正则表达式

show tables;

create table if not exists `reg`

(

name varchar(100) not null

);

select *

from reg;

/*insert into reg values ('xxs');

insert into reg values ('xxs,a,b,d,c,,ds,d,');

insert into reg values ('xxs238sd');

insert into reg values ('37943');

insert into reg values ('2.31');

insert into reg values ('2.31d');

insert into reg values ('2.2.1');*/

#2***d结尾的。

select *

from reg

where regexp_like(name, '^2.*d$');

# 不等于1,2,3

select *

from reg

where regexp_like(name, '[^123]');

#

select *

from reg

where regexp_like(name, '[^0-9\.]');

select *

from reg

where regexp_like(name, '[a-z]+');

select *

from reg

where regexp_like(name, ',');

select *

from reg

where regexp_like(name, '2\\..*?');

select name, regexp_like(name, '(2{1})')

from reg;

select *

from reg

where regexp_like(name, '[0-9]{2,3}');

select name, regexp_instr(name, '[,]+', 1, 2)

from reg;

select name, regexp_replace(name, 'x', 'a')

from reg;

select 'Bn' REGEXP '^Ba*n';

select 'pix' REGEXP '^(pi|apa)$';

# -> 0(表示不匹配)

select substr('xsjsss', 3, 2);

select name

from reg;

select *

from reg

where regexp_like(name, '2|,');

select name, regexp_substr(name, '[^,]+', 1, 1)

from reg;

select *

from reg;

select *

from reg

where regexp_like(name, '.*2.*d$');

# 循环查找

DROP TABLE IF EXISTS `t_areainfo`;

CREATE TABLE `t_areainfo`

(

`id` int(11) NOT null AUTO_INCREMENT,

`level` int(11) DEFAULT '0',

`name` varchar(255) DEFAULT '0',

`parentId` int(11) DEFAULT '0',

`status` int(11) DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE = InnoDB

AUTO_INCREMENT = 65

DEFAULT CHARSET = utf8;

select *

from t_areainfo

where parentId = 4

or id = 4

order by id, parentId;

# 通过当前id获取父类

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

CREATE FUNCTION `queryChildrenAreaInfo`(areaId INT)

RETURNS VARCHAR(4000)

DETERMINISTIC

BEGIN

DECLARE sTemp VARCHAR(4000);

DECLARE sTempChd VARCHAR(4000);

SET sTemp = '$';

SET sTempChd = cast(areaId as char);

WHILE sTempChd is not NULL

DO

SET sTemp = CONCAT(sTemp, ',', sTempChd);

SELECT group_concat(id) INTO sTempChd FROM t_areainfo where FIND_IN_SET(parentId, sTempChd) > 0;

END WHILE;

return sTemp;

END;

# select concat('a',',','b');

select cast(1 as char);

# 创建存储过程。

select queryChildrenAreaInfo(4);

select *

from t_areainfo

where FIND_IN_SET(id, queryChildrenAreaInfo(4));

drop function if exists test;

create function `test`()

returns varchar(100)

DETERMINISTIC

begin

return (select group_concat(name) name from reg where name like 'x%');

end;

select test() name;

# 创建一个存储函数

drop function if exists test_pro;

create function test_pro()

returns int

DETERMINISTIC

BEGIN

declare ids int default 1;

select id into ids from sales where id = 2;

set ids = 10;

return ids;

end;

select test_pro() ids;

# loop循环

drop function if exists test_add;

create function test_add(max int)

returns int

DETERMINISTIC

BEGIN

declare num int default 0;

declare sum int default 0;

add_num:

loop

set num = num + 1;

set sum = sum + num;

if num >= max then

leave add_num;

end if;

end loop add_num;

return sum;

end;

select test_add(12);

# for 循环

create function findParent(chdId int)

returns varchar(4000)

DETERMINISTIC

BEGIN

declare allId varchar(4000);

declare nowIds varchar(4000);

set allId = '$';

set nowIds = cast(chdId as char);

while nowIds is not null

do

set allId = concat(allId, ',', nowIds);

select group_concat(parentId) into nowIds from t_areainfo where find_in_set(id, nowIds);

end while;

return allid;

end;

select findParent(26);

select * from t_areainfo;

select replace(group_concat(name),',','-') addr from t_areainfo where find_in_set(id,findParent(5))

order by id;

```

一键复制

编辑

Web IDE

原始数据

按行查看

历史