增加LSB1,字段ID,自增,内容1-10000 SELECT MID(ss,id+1,INSTR(MID(ss,id+1),'@')-1) AS ss2 FROM ( SELECT CONCAT('@','手机@20100112@1人/个@800@5hg@ere@ghj','@') AS ss) a LEFT JOIN zz.lsb1 b ON LENGTH(a.ss)>=b.id WHERE MID(ss,id,1)='@' AND MID(ss,id+1,INSTR(MID(ss,id+1),'@')-1)<>' '将CONCAT('@','手机@20100112@1人/个@800@5hg@ere@ghj','@') 中的'手机@20100112@1人/个@800@5hg@ere@ghj'修改为你的字段名即可
mysql> select *from asdaa; +------+---------------------------+ | id | content | +------+---------------------------+ | 1 | 手机@20100112@1人/个@800@ | +------+---------------------------+ 1 row in set (0.00 sec)mysql> select *from asdbb; Empty set (0.00 sec)mysql> insert into asdbb -> select SUBSTRING_INDEX(content,'@',1) as a, -> SUBSTRING_INDEX(SUBSTRING_INDEX(content,'@',2),'@',-1) as b, -> SUBSTRING_INDEX(SUBSTRING_INDEX(content,'@',3),'@',-1) as c , -> SUBSTRING_INDEX(SUBSTRING_INDEX(content,'@',4),'@',-1) as d -> from asdaa -> ; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> select *from asdbb; +------+----------+--------+------+ | te | dt | pe | num | +------+----------+--------+------+ | 手机 | 20100112 | 1人/个 | 800 | +------+----------+--------+------+ 1 row in set (0.00 sec)
这个有点太高深了,刚接触mysql,看了半天没看明白!
1、建表会吧 增加LSB1,字段ID,自增,内容1-10000 2、 SELECT MID(ss,id+1,INSTR(MID(ss,id+1),'@')-1) AS ss2 FROM ( SELECT 你的字段名 AS ss) a LEFT JOIN zz.lsb1 b ON LENGTH(a.ss)>=b.id WHERE MID(ss,id,1)='@' AND MID(ss,id+1,INSTR(MID(ss,id+1),'@')-1)<>' '可以处理表中的所有记录
SELECT MID(ss,id+1,INSTR(MID(ss,id+1),'@')-1) AS ss2
FROM (
SELECT CONCAT('@','手机@20100112@1人/个@800@5hg@ere@ghj','@') AS ss) a LEFT JOIN zz.lsb1 b ON LENGTH(a.ss)>=b.id
WHERE MID(ss,id,1)='@' AND MID(ss,id+1,INSTR(MID(ss,id+1),'@')-1)<>' '将CONCAT('@','手机@20100112@1人/个@800@5hg@ere@ghj','@')
中的'手机@20100112@1人/个@800@5hg@ere@ghj'修改为你的字段名即可
+------+---------------------------+
| id | content |
+------+---------------------------+
| 1 | 手机@20100112@1人/个@800@ |
+------+---------------------------+
1 row in set (0.00 sec)mysql> select *from asdbb;
Empty set (0.00 sec)mysql> insert into asdbb
-> select SUBSTRING_INDEX(content,'@',1) as a,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(content,'@',2),'@',-1) as b,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(content,'@',3),'@',-1) as c ,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(content,'@',4),'@',-1) as d
-> from asdaa
-> ;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> select *from asdbb;
+------+----------+--------+------+
| te | dt | pe | num |
+------+----------+--------+------+
| 手机 | 20100112 | 1人/个 | 800 |
+------+----------+--------+------+
1 row in set (0.00 sec)
这个有点太高深了,刚接触mysql,看了半天没看明白!
增加LSB1,字段ID,自增,内容1-10000
2、
SELECT MID(ss,id+1,INSTR(MID(ss,id+1),'@')-1) AS ss2
FROM (
SELECT 你的字段名 AS ss) a LEFT JOIN zz.lsb1 b ON LENGTH(a.ss)>=b.id
WHERE MID(ss,id,1)='@' AND MID(ss,id+1,INSTR(MID(ss,id+1),'@')-1)<>' '可以处理表中的所有记录