Creating Tables in TiDB

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

Original topic: tidb建表

| username: TiDBer_QHSxuEa1

Does TiDB support setting a default value for a field based on another field when creating a table? Similar to the logic below:

| username: 啦啦啦啦啦 | Original post link

Take a look at whether generated columns can meet your needs

| username: MrSylar | Original post link

What was the original database syntax for this?

| username: tidb菜鸟一只 | Original post link

CREATE TABLE test(
flag char(1) default ‘1’,
change_flag char(1) as (if(flag=‘1’,‘2’,‘3’))
);

| username: tidb菜鸟一只 | Original post link

It should be Oracle that had it first. Oracle 10g already had this syntax…

| username: zhanggame1 | Original post link

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:
image

| username: cassblanca | Original post link

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.

| username: zhanggame1 | Original post link

How to understand the JSON example if(flag=‘1’,‘2’,‘3’)?

| username: cassblanca | Original post link

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.

| username: tidb菜鸟一只 | Original post link

Determine if the flag is 1. If it is, change the flag to 2; if not, change the flag to 3.

| username: redgame | Original post link

Sure. I’ve always been unsure about what this use case is.

| username: zhanggame1 | Original post link

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?

| username: tidb菜鸟一只 | Original post link

It can be seen as an upgraded version of ifnull.