I am new to MySQL. I have designed few tables with Char datatype. Now I want to change all the char datatype columns to Varchar with respective size mentioned in Char.
For example.
Existing - Phone Char(10)
Expected - Phone Varchar(10)
There are around 50 Plus tables. I know how to alter the datatype for each and every table and column but is there any better way to change the datatype in a single shot.
解决方案
If you can't write a script to do the job, write a script that writes a script to do the job!
You want a bunch of ALTER TABLE statements:
SET SESSION group_concat_max_len = 1000000; -- Bumps the limit of 1028
SELECT GROUP_CONCAT(
CONCAT(
'ALTER TABLE `',
table_name,
'` MODIFY COLUMN `',
column_name,
'` VARCHAR(',
character_maximum_length,
')'
)
SEPARATOR ';\n') your_alter_statements
FROM information_schema.columns
WHERE table_schema = 'concrete'
AND data_type = 'char';
This'll result in:
ALTER TABLE `table1` MODIFY COLUMN `col1` VARCHAR(10);
ALTER TABLE `table1` MODIFY COLUMN `col2` VARCHAR(10);
ALTER TABLE `table2` MODIFY COLUMN `col1` VARCHAR(10);
ALTER TABLE `table3` MODIFY COLUMN `col1` VARCHAR(10);
ALTER TABLE `table3` MODIFY COLUMN `col2` VARCHAR(10);
Run that and you can go home early!
UPDATE: Stopped the truncation by adding group_concat_max_len. Made the length dynamic based on the columns length.