Add Non-Null Field

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

Original topic: 添加非空字段

| username: Daniel-W

[TiDB Usage Environment] Testing
[TiDB Version] v6.5
If I add a non-null field to the current table, will the original data change?

| username: Daniel-W | Original post link

This is a situation I tested

| username: Kongdom | Original post link

I tried it, and it performs the same as MySQL 8.0.

| username: Daniel-W | Original post link

Please elaborate, expert.

| username: QH琉璃 | Original post link

Didn’t understand the meaning.

| username: forever | Original post link

I tested the performance and it was consistent, all padding with 0.

| username: 小龙虾爱大龙虾 | Original post link

It’s always been like this.

| username: Daniel-W | Original post link

Is there any documentation that explains this in detail?

| username: Kongdom | Original post link

Expand what? I just verified it in 8.0, and it’s the same as what you verified: varchar is given a null value, and int is given 0.

| username: Kongdom | Original post link

Did you use SQL Server before? SQL Server will report an error.

ALTER TABLE only allows adding columns that meet the following conditions: the column can contain Null values; or the column has a specified DEFAULT definition; or the column to be added is an identity column or a timestamp column; or, if none of the previous conditions are met, the table must be empty to allow adding this column. The column “c_temp” cannot be added to the non-empty table “tb_store” because it does not meet the above conditions.

| username: TiDBer_jYQINSnf | Original post link

I used to think it would throw an error, but now it automatically fills in the missing values. Awesome!
Does version 5.7 automatically fill in the missing values or throw an error?

| username: Kongdom | Original post link

I no longer have a 5.7 environment here; everything has been upgraded to 8.0.

| username: TIDB-Learner | Original post link

It is related to sql mode, or is it set to 0 by default in version 8.0 and above?

| username: 小龙虾爱大龙虾 | Original post link

I couldn’t find it, but I remember it being like this.

| username: mono | Original post link

It’s the same as MySQL. When adding a NOT NULL constraint, it is generally used together with a default value.

| username: zhaokede | Original post link

Directly padding int with 0

| username: 随缘天空 | Original post link

This is related to the type of field you added and whether it has a default value. Adding a non-null integer will change the original data, while if it’s a string, it might be empty.

| username: xiaoqiao | Original post link

Adding a new field will supplement the default value.

| username: TiDBer_RjzUpGDL | Original post link

int padZero

| username: TiDBer_JUi6UvZm | Original post link

Use NOT NULL constraint + default value