Rechercher dans le manuel MySQL
13.1.9.2 ALTER TABLE and Generated Columns
ALTER TABLE
operations permitted for
generated columns are ADD
,
MODIFY
, and CHANGE
.
Generated columns can be added.
The data type and expression of generated columns can be modified.
Generated columns can be renamed or dropped, if no other column refers to them.
Virtual generated columns cannot be altered to stored generated columns, or vice versa. To work around this, drop the column, then add it with the new definition.
Nongenerated columns can be altered to stored but not virtual generated columns.
Stored but not virtual generated columns can be altered to nongenerated columns. The stored generated values become the values of the nongenerated column.
ADD COLUMN
is not an in-place operation for stored columns (done without using a temporary table) because the expression must be evaluated by the server. For stored columns, indexing changes are done in place, and expression changes are not done in place. Changes to column comments are done in place.For non-partitioned tables,
ADD COLUMN
andDROP COLUMN
are in-place operations for virtual columns. However, adding or dropping a virtual column cannot be performed in place in combination with otherALTER TABLE
operations.For partitioned tables,
ADD COLUMN
andDROP COLUMN
are not in-place operations for virtual columns.InnoDB
supports secondary indexes on virtual generated columns. Adding or dropping a secondary index on a virtual generated column is an in-place operation. For more information, see Section 13.1.20.10, “Secondary Indexes and Generated Columns”.When a
VIRTUAL
generated column is added to a table or modified, it is not ensured that data being calculated by the generated column expression will not be out of range for the column. This can lead to inconsistent data being returned and unexpectedly failed statements. To permit control over whether validation occurs for such columns,ALTER TABLE
supportsWITHOUT VALIDATION
andWITH VALIDATION
clauses:With
WITHOUT VALIDATION
(the default if neither clause is specified), an in-place operation is performed (if possible), data integrity is not checked, and the statement finishes more quickly. However, later reads from the table might report warnings or errors for the column if values are out of range.With
WITH VALIDATION
,ALTER TABLE
copies the table. If an out-of-range or any other error occurs, the statement fails. Because a table copy is performed, the statement takes longer.
WITHOUT VALIDATION
andWITH VALIDATION
are permitted only withADD COLUMN
,CHANGE COLUMN
, andMODIFY COLUMN
operations. Otherwise, anER_WRONG_USAGE
error occurs.If expression evaluation causes truncation or provides incorrect input to a function, the
ALTER TABLE
statement terminates with an error and the DDL operation is rejected.An
ALTER TABLE
statement that changes the default value of a columncol_name
may also change the value of a generated column expression that refers to the column usingcol_name
, which may change the value of a generated column expression that refers to the column usingDEFAULT(
. For this reason,col_name
)ALTER TABLE
operations that change the definition of a column cause a table rebuild if any generated column expression usesDEFAULT()
.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-alter-table-generated-columns.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.