Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb建表

Does TiDB support setting a default value for a field based on another field when creating a table? Similar to the logic below:
Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb建表
Does TiDB support setting a default value for a field based on another field when creating a table? Similar to the logic below:
CREATE TABLE test(
flag char(1) default ‘1’,
change_flag char(1) as (if(flag=‘1’,‘2’,‘3’))
);
It should be Oracle that had it first. Oracle 10g already had this syntax…
I don’t understand. Use this statement to create a table, and then look at the create table statement like this:
CREATE TABLE `test` (
`flag` char(1) DEFAULT '1',
`change_flag` char(1) GENERATED ALWAYS AS (if(`flag` = _utf8mb4'1', _utf8mb4'2', _utf8mb4'3')) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Inserting values into flag
returns the following:
In Oracle, it’s called a pseudo column. In PostgreSQL, it’s called a computed column. In MySQL, it’s called a computed column. In TiDB, it’s called a generated column. They all mean the same thing. Refer to the following SQL syntax:
Generated Columns | PingCAP Documentation Center
P.S. Generated columns officially became GA in TiDB version 7.1.0, and there are many restrictions when using indexes on generated columns.
How to understand the JSON example if(flag=‘1’,‘2’,‘3’)?
It can be understood that the value of change_flag can only be selected from the three code values 1, 2, and 3 in the flag field. Other values are invalid. This might be a design to assist the application program in performing logical validation.
Determine if the flag is 1. If it is, change the flag to 2; if not, change the flag to 3.
I still don’t understand how the conversion works. Why is change_flag 1 in flat and 2 in value? Is there any convention for this?