INSERT INTO user_testx (nickname,logintime,loginip,`lock`) SELECT nickname,logintime,loginip,`lock` FROM user_test WHERE nickname NOT LIKE '%,%';//插入不带,的数据到新表 INSERT INTO user_testx (nickname,logintime,loginip,`lock`) SELECT SUBSTRING_INDEX(nickname,',',1) AS nickname,logintime,loginip,`lock` FROM user_test WHERE nickname LIKE '%,%';//插入带,的前半部分数据到新表 INSERT INTO user_testx (nickname,logintime,loginip,`lock`) SELECT SUBSTRING_INDEX(nickname,',',-1) AS nickname,logintime,loginip,`lock` FROM user_test WHERE nickname LIKE '%,%';//插入带,的后半部分数据到新表自己对着写
INSERT INTO user_test (nickname,logintime,loginip,`lock`) SELECT SUBSTRING_INDEX(nickname,',',-1) AS nickname,logintime,loginip,`lock` FROM user_test WHERE nickname LIKE '%,%'; UPDATE user_test SET nickname=SUBSTRING_INDEX(nickname,',',1) WHERE nickname LIKE '%,%'; 。 懒死了,自己看
SELECT SUBSTRING_INDEX(nickname,',',-1) AS nickname,logintime,loginip,`lock` FROM user_test WHERE nickname LIKE '%,%' UNION SELECT SUBSTRING_INDEX(nickname,',',1) AS nickname,logintime,loginip,`lock` FROM user_test WHERE nickname LIKE '%,%' UNION SELECT nickname,logintime,loginip,`lock` FROM user_test WHERE nickname NOT LIKE '%,%';
如果多,则可以考虑用一个辅助表,其中 1,2,3,4...N 然后用substring_index 来取值。
INSERT INTO user_testx (nickname,logintime,loginip,`lock`) SELECT nickname,logintime,loginip,`lock` FROM user_test WHERE nickname NOT LIKE '%,%';//插入不带,的数据到新表
INSERT INTO user_testx (nickname,logintime,loginip,`lock`) SELECT SUBSTRING_INDEX(nickname,',',1) AS nickname,logintime,loginip,`lock` FROM user_test WHERE nickname LIKE '%,%';//插入带,的前半部分数据到新表
INSERT INTO user_testx (nickname,logintime,loginip,`lock`) SELECT SUBSTRING_INDEX(nickname,',',-1) AS nickname,logintime,loginip,`lock` FROM user_test WHERE nickname LIKE '%,%';//插入带,的后半部分数据到新表自己对着写
UPDATE user_test SET nickname=SUBSTRING_INDEX(nickname,',',1) WHERE nickname LIKE '%,%';
。
懒死了,自己看
UNION
SELECT SUBSTRING_INDEX(nickname,',',1) AS nickname,logintime,loginip,`lock` FROM user_test WHERE nickname LIKE '%,%'
UNION
SELECT nickname,logintime,loginip,`lock` FROM user_test WHERE nickname NOT LIKE '%,%';