mysql修改表字段小数点精度_技术篇-将字段类型decimal批量处理从2位改为4位小数点sql

声明：本人对一些版权并不深入了解，但是这个脚本是真的好，帮我解决了问题，特此收藏，并且注明了原文链接，如有侵权，请告知删除。

/*

脚本作用:原来定义为decimal(18,2)类型的所有统一修改为decimal(19,4)。

作者：newsxy

来源：CSDN

原文：/newsxy/article/details/51280414

版权声明：本文为博主原创文章，转载请附上博文链接！

*/

-- 关闭约束

declare tb cursor for

SELECT sql='alter table ['+d.name+'] NOCHECK CONSTRAINT all'

FROM syscolumns a left join systypes b on a.xtype=b.xusertype

inner join sysobjects d on a.id=d.id and d.xtype='U'and d.name<>'dtproperties'

where b.name in('decimal') GROUP BY d.name

declare @sql1 varchar(1000)

open tb

fetch next from tb into @sql1

while @@fetch_status = 0

begin

print @sql1

exec(@SQL1)

fetch next from tb into @sql1

end

close tb

deallocate tb

-- 修改字段数据类型

declare tb cursor for

SELECT sql='alter table ['+d.name+'] alter column ['+a.name+'] decimal(19,4)'

FROM syscolumns a left join systypes b on a.xtype=b.xusertype

inner join sysobjects d on a.id=d.id and d.xtype='U'and d.name<>'dtproperties'

where b.name in('decimal') order by d.name,a.name

declare @sql2 varchar(1000)

open tb

fetch next from tb into @sql2

while @@fetch_status = 0

begin

print @sql2

exec(@SQL2)

fetch next from tb into @sql2

end

close tb

deallocate tb

-- 恢复约束

declare tb cursor for

SELECT sql='alter table ['+d.name+'] CHECK CONSTRAINT ALL'

FROM syscolumns a left join systypes b on a.xtype=b.xusertype

inner join sysobjects d on a.id=d.id and d.xtype='U'and d.name<>'dtproperties'

where b.name in('decimal') GROUP BY d.name

declare @sql3 varchar(1000)

open tb

fetch next from tb into @sql3

while @@fetch_status = 0

begin

print @sql3

exec(@SQL3)

fetch next from tb into @sql3

end

close tb

deallocate tb

---------------------

mysql修改表字段小数点精度_技术篇-将字段类型decimal批量处理从2位改为4位小数点sql 解决数据库存储精度...

如果觉得《mysql修改表字段小数点精度_技术篇-将字段类型decimal批量处理从2位改为4位小数点sql》对你有帮助，请点赞、收藏，并留下你的观点哦！