为数据库中的所有列设置默认值

本文介绍了为数据库中的所有列设置默认值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用 PHP + MySQL 应用程序.该应用程序对我来说工作正常.但是当我将它托管在另一台服务器上时，出现了 MySQL 错误:

I am working in a PHP + MySQL application. The application is working fine for me. But when I hosted it in another server, I got a MySQL error:

错误代码:1364.字段field"没有默认值

Error Code: 1364. Field 'field' doesn't have a default value

我知道这是 MySQL 版本的问题，我们应该为所有列设置默认值.但目前我有 100 多张桌子.因此，我需要将所有表中还没有默认值的所有列的默认值设置为 NULL.

I know this is a problem with the MySQL version and we should setup default values for all columns. But currently I have more than 100 tables. So I need to set default value to NULL for all columns in all tables that has no default value yet.

我无法使用 strict mode 选项，因为服务器是共享的.是否可以一步设置而不是为每个表设置?如果不可能，请告诉我设置它的最简单方法.

I can't make use of the strict mode option, because the server is a shared one. Is it possible to setup in a single step rather than setting for each and every table ? If not possible tell me the easiest way to setup it.

任何帮助将不胜感激

推荐答案

对于遇到此问题的其他任何人，需要一些编码才能自动执行，但您可以按照以下方式进行操作:

For anyone else with this problem, it will take a bit of coding to perform automatically, but the following would be how you would do so:

首先运行以下查询:

SELECT table_schema,table_name,column_name,data_type FROM information_schema.columns WHERE IS_NULLABLE='NO' AND column_default is null AND column_key=''

接下来，对上述查询返回的每一行执行以下操作:

Next, for each row returned from the above query perform the following:

If data_type contains 'int' set default to 0 else if data_type='datetime' set default to '0000-00-00 00:00:00' else if data_type='date' set default to '0000-00-00' else if data_type='time' set default to '00:00:00' else set default to ''

创建并运行以下查询，并将所有 [[...]] 变量替换为其适当的值:

create and run the following query with all [[...]] variables replaced with their proper values:

ALTER TABLE `[[table_schema]]`.`[[table_name]]` ALTER COLUMN `[[column_name]]` SET DEFAULT '[[default]]'

这应该替换所有数据库、所有表、所有设置为 NOT NULL 且不是主键且未设置默认值的列的默认值.

This should replace the default values for all databases, all tables, all columns that are set to be NOT NULL and are not primary keys and have no default value set.