如果我这样做:
ALTER TABLE testtable MODIFY mycolumn NEWDATATYPE;
我失去了其他定义,例如非空、注释、默认值......有没有办法做到这一点?
在PostgreSQL中,我使用了:
ALTER TABLE testtable ALTER COLUMN mycolumn NEWDATATYPE;
它做了应该做的事情:更改列数据类型,而不触及任何其他定义,仅在数据类型不兼容时给出错误等等(但您可以指定 USING)。
我将尝试一种解决方法,但我执行了一个查询来识别不同表中的几列以更新数据类型,现在我已经确定此数据已丢失,因此我也必须重做这些信息。
如手册页中所述,ALTER TABLE
需要定义所有新的类型属性。
但是,有一种方法可以克服这一点。您可以使用INFORMATION_SCHEMA
元数据来重建所需的ALTER
查询。例如,如果我们有简单的表格:
mysql> DESCRIBE T;+-------+------------------+------+-----+---------+----------------+|字段 |类型 |空 |钥匙 |默认 |额外 |+-------+------------------+------+-----+---------+----------------+|身份证 |int(11) 无符号 |否 |PRI |空 |auto_increment ||价值 |瓦尔查尔(255) |否 | |空 | |+-------+------------------+------+-----+---------+----------------+2 行(0.01 秒)
然后我们可以用以下命令重现我们的 alter 语句:
SELECT
CONCAT(
COLUMN_NAME,
' @new_type',
IF(IS_NULLABLE='NO', ' NOT NULL ', ' '),
EXTRA
) AS s
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA='test'
AND
TABLE_NAME='t'
结果将是:
+--------------------------------------+|s |+--------------------------------------+|ID @new_type不空 auto_increment ||值@new_type不为空 |+--------------------------------------+
在这里,我留下@new_type
来表明我们可以为此使用变量(甚至直接将我们的新类型替换为查询)。变量为:
设置我们的变量。
mysql> SET @new_type := 'VARCHAR(10)', @column_name := 'value'; Query OK, 0 rows affected (0.00 sec)
为预准备语句准备变量(这是长查询,但我在上面留下了解释):
SET @sql = (SELECT CONCAT('ALTER TABLE t CHANGE `',COLUMN_NAME, '` `', COLUMN_NAME, '` ', @new_type, IF(IS_NULLABLE='NO', ' NOT NULL ', ' '), EXTRA) AS s FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t' AND COLUMN_NAME=@column_name);
准备声明:
mysql> prepare stmt from @sql; Query OK, 0 rows affected (0.00 sec) Statement prepared
最后,执行它:
mysql> execute stmt; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0
然后,我们将数据类型更改为VARCHAR(10)
,并保存所有其余说明符:
mysql> DESCRIBE T;+-------+------------------+------+-----+---------+----------------+|字段 |类型 |空 |钥匙 |默认 |额外 |+-------+------------------+------+-----+---------+----------------+|身份证 |int(11) 无符号 |否 |PRI |空 |auto_increment ||价值 |瓦尔查尔(10) |否 | |空 | |+-------+------------------+------+-----+---------+----------------+2 行设置(0.00 秒)
ALTER TABLE tableName
MODIFY COLUMN columnName datatype
在 ALTER table_name
中使用 CHANGE
或 MODIFY
时,column_definition
必须包括数据类型和应应用于新列的所有属性,而不是索引属性,如 PRIMARY KEY
或 UNIQUE
。原始定义中存在但未为新定义指定的属性不会结转。
假设列col1
定义为INT UNSIGNED DEFAULT 1
COMMENT 'my column'
,并且按如下方式修改该列:
ALTER TABLE t1 MODIFY col1 BIGINT;
生成的列将定义为 BIGINT
,但不包括属性UNSIGNED DEFAULT 1
COMMENT 'my column'
。要保留它们,声明应为:
ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
使用 CHANGE
或 MODIFY
更改数据类型时,MySQL
尝试尽可能将现有列值转换为新类型。
请参阅文档。