.alter column.alter column

更改现有表列的数据类型。Alters the data type of an existing table column.

警告

更改列的数据类型后,以后的查询中将忽略该列中不属于新数据类型的任何预先存在的数据,并将其替换为 null 值When altering the data type of a column, any pre-existing data in that column which is not of the new data type will be ignored in future queries and will be replaced by a null value. 使用 alter column 后,即使通过使用另一个命令将列类型更改回以前的值,也无法恢复列中的数据。After using alter column, that data cannot be recovered, even through using another command to alter the column type back to a previous value. 有关在不丢失数据的情况下更改列类型的建议过程,请参阅下文See below for the recommended procedure for changing the type of a column without losing data.

语法Syntax

.alter column [DatabaseName .] TableName . ColumnName type = ColumnNewType.alter column [DatabaseName .] TableName . ColumnName type = ColumnNewType

示例Example

.alter column ['Table'].['ColumnX'] type=string

在不丢失数据的情况下更改列类型Changing column type without data loss

若要在保留历史数据的同时更改列类型,请创建一个具有正确类型的新表。To change column type while retaining the historical data, create a new, properly typed table.

对于要在其中更改列类型的每个表 T1,请执行以下步骤:For each table T1 you'd like to change a column type in, execute the following steps:

  1. 使用正确的架构(正确的列类型)创建一个表 T1_primeCreate a table T1_prime with the correct schema (the right column types).
  2. 使用 .rename tables 命令交换表,这一命令允许交换表名称。Swap the tables using .rename tables command, which allows swapping table names.
.rename tables T_prime=T1, T1=T_prime

命令完成后,新的数据将流向现在具有正确类型的 T1,并且历史数据在 T1_prime 中可用。When the command completes, the new data flows to T1 that is now typed correctly and the historical data is available in T1_prime.

T1_prime 数据离开保留窗口之前,需要更改触及 T1 的查询,以执行与 T1_prime的联合。Until T1_prime data goes out of the retention window, queries touching T1 need to be altered to perform union with T1_prime.