mysql> SELECT CONCAT ( CAST(1 as char) , '2') AS test
+------+
| test |
+------+
| 12 |
+------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT ( Convert(1, char) , '2') AS test
+------+
| test |
+------+
| 12 |
+------+
1 row in set (0.00 sec)
字符转数字
mysql> SELECT CAST('1' as SIGNED) + 100 AS test
+------+
| test |
+------+
| 101 |
+------+
1 row in set (0.00 sec)
mysql> SELECT Convert('1' , SIGNED) + 100 AS test
+------+
| test |
+------+
| 101 |
+------+
1 row in set (0.00 sec)
写个存储过程吧,我这里只有sql server的测试环境,我照着语法改成mysql的,没法测试如果有问题再问
create proc testas
begin
declare @i int
update 表名 set MODEL_ID=-1
update 表名 set MODEL_ID=CONVERT(int,MODEL_NAME)
where ISNUMERIC(MODEL_NAME)=1
while(exists(select 1 from 表名 where MODEL_ID=-1))
begin
select @i=1001
while(exists(select 1 from 表名 where MODEL_ID=@i))
select @i=@i+1
update 表名 set MODEL_ID=@i
where MODEL_NAME =
(select MODEL_NAME from 表名 where MODEL_ID=-1 limit 1)
end
end
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)