Modify the field length of all tables in a database that contain the same field in MySQL

2024-07-12

background

Due to the business scenario, a field such as phone_name involves many tables. Currently, many tables have redundant fields. However, the field length given in the early stage is only varchar(100), which does not meet the current needs. It is necessary to increase the field length of all tables to varchar(255). If you modify it manually one by one, there are hundreds of tables, which is very time-consuming. Therefore, the following method is thought of as a memo.

plan

Modify this field in these tables to make it mandatory DDL statement


SELECT
concat("ALTER TABLE `",table_name,"` MODIFY COLUMN `phone_name`  varchar(255)  NOT NULL COMMENT '手机名称';")
FROM information_schema.columns
WHERE
table_schema = 'db_lingyejun' and column_name='phone_name' and character_maximum_length < 255 and is_nullable = 'NO';

Modify the DDL statements in these tables to allow this field to be nullable


SELECT
concat("ALTER TABLE `",table_name,"` MODIFY COLUMN `phone_name`  varchar(255)  DEFULT NULL COMMENT '手机名称';")
FROM information_schema.columns
WHERE
table_schema = 'db_lingyejun' and column_name='phone_name' and character_maximum_length < 255 and is_nullable = 'YES';

If this article is helpful to you, please give "Lingyejun" a like. Thank you for your support.

First release link:https://www.cnblogs.com/lingyejun/p/18293068

Technology Sharing