无论如何都将表中的所有列修改为“非空" [英] Modify all columns in a table to 'not null' no matter what

问题描述

是否有一个简单的命令可以将表中的所有列修改为非空?

Is there a simple command to modify all columns in a table to not null?

因为现在我必须找到每列的名称和类型才能写出这样的内容:

Because now I have to find the name and the type of each column to write something like this:

    ALTER TABLE testing CHANGE testing_text testing_text VARCHAR(10) NOT NULL;
    ...

这对我来说是很多工作.

and that is a lot of work for me.

推荐答案

一种快速的方法是将你的alter 语句写入文件

A quick way is to write your alter statements into a file

select
concat('ALTER TABLE ', c.TABLE_NAME, ' CHANGE ', c.COLUMN_NAME, ' ', c.COLUMN_NAME, ' ', c.COLUMN_TYPE, ' NOT NULL;') as alter_statement
into outfile '/tmp/alter.txt'
from information_schema.COLUMNS c
where 
c.IS_NULLABLE = 'YES'
and c.TABLE_SCHEMA = 'your_database_name';

然后执行文件的内容

source /tmp/alter.txt

你完成了...

在操场数据库中对其进行了测试，它对我有用，但您可能仍想在执行之前仔细检查文件:)

Tested it in a playground DB and it worked for me, still you might want to double check the file before executing :)

P.S.:我还没有检查如何处理 NULL 值.IIRC 你必须有一个默认值吗?目前不确定.使用前请先测试一下.

P.S.: I haven't checked how NULL values are handled. IIRC you have to have a default value? Not sure right now. Please test this before using it.

编辑 1:每个表有一个语句:

EDIT 1: To have one statement per table:

select
concat('ALTER TABLE ', c.TABLE_NAME, GROUP_CONCAT(' MODIFY COLUMN ', c.COLUMN_NAME, ' ', c.COLUMN_TYPE, ' NOT NULL')) as alter_statement
from information_schema.COLUMNS c
where 
c.IS_NULLABLE = 'YES'
and c.TABLE_SCHEMA = 'your_database_name'
group by c.TABLE_NAME

编辑 2:

这个有效

select concat(alter_statement, ';')
into outfile '/tmp/alter.txt'
from (
select
concat('ALTER TABLE ', c.TABLE_NAME, GROUP_CONCAT(' CHANGE ', c.COLUMN_NAME, ' ', c.COLUMN_NAME, ' ', c.COLUMN_TYPE, ' NOT NULL')) as alter_statement

from information_schema.COLUMNS c
where 
c.IS_NULLABLE = 'YES'
and c.TABLE_SCHEMA = 'playground'
group by c.TABLE_NAME
) sq

，但 group_concat() 的长度有限，因此如果表中有太多列，您可能会出现语法错误.那你还是上面第一个选项，或者你看看本手册条目:

, but group_concat() is limited in length, so you might get syntax errors if you have too many columns in a table. Then you still have the first option from above, or you have a look at this manual entry:

结果被截断为 group_concat_max_len 系统变量给定的最大长度，其默认值为 1024.该值可以设置得更高，尽管返回值的有效最大长度受max_allowed_pa​​cket.在运行时更改 group_concat_max_len 值的语法如下，其中 val 是一个无符号整数:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

这篇关于无论如何都将表中的所有列修改为“非空"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！