MYSQL-将所有列的默认值设置为NULL，而未设置默认值的情况 [英] MYSQL - set default as NULL to all columns, where default is not set

问题描述

我大约有12个数据库，每个数据库有50个表，其中大多数都有30多个列； db在严格模式下以OFF运行，但是现在我们不得不将db迁移到cleardb服务，默认情况下，严格模式为ON. 所有具有"Not Null"约束的表，由于未传递默认值，所以插入已停止工作；如果没有提供值，则在严格模式下将其设置为OFF时，MYSQL将假定列数据类型的默认值. 是否可以使用脚本来获取有关所有表的所有列的元数据，并生成一个脚本来更改具有此类列的所有表，以将默认值更改为"Null"

I have about 12 databases, each with 50 tables and most of them with 30+ columns; the db was running in strict mode as OFF, but now we had to migrate the db to cleardb service which by default has strict mode as ON. all the tables that had "Not Null" constraint, the inserts have stopped working, just because the default values are not being passed; while in case of strict mode as OFF if the value are not provided, the MYSQL will presume the default value of the column datatype. Is there a script I can use to get the metadata about all the columns of all tables and generate a script to alter all the tables with such columns to change the default to "Null"

推荐答案

您应该考虑使用information_schema表来生成DDL语句以更改表.这种查询将为您提供有问题的列的列表.

You should consider using the information_schema tables to generate DDL statements to alter the tables. This kind of query will get you the list of offending columns.

SELECT CONCAT_WS('.',TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) col  
  FROM information_schema.COLUMNS
 WHERE IS_NULLABLE = 0
   AND LENGTH(COLUMN_DEFAULT) = 0 
   AND TABLE_SCHEMA IN ('db1', 'db2', 'db3')

您可以执行类似的操作来生成ALTER语句以更改表.但要注意，当您更改某些内容时，MySQL喜欢重写表.这可能需要一些时间.

You can do similar things to generate ALTER statements to change the tables. But beware, MySQL likes to rewrite tables when you alter certain things. It might take time.

请勿尝试直接UPDATE information_schema！

DO NOT attempt to UPDATE the information_schema directly!

当您连接到SaaS服务时，您可以尝试更改strict_mode设置，以便您的软件可以兼容工作.

You could try changing the strict_mode setting when you connect to the SaaS service, so your software will work compatibly.

这是一个很大的项目，对于cleardb来说可能是重要的业务.为什么不向他们寻求帮助来更改strict_mode设置?

This is a large project and is probably important business for cleardb. Why not ask them for help in changing the strict_mode setting?

这篇关于MYSQL-将所有列的默认值设置为NULL，而未设置默认值的情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！