CODEWITHSUNDEEP
mysqldatabase
Vivek Goel
Vivek Goel

Reputation: 24150

How does default value internally work in Database?

How does default value internally work in Database?

If I alter a table:

alter table foo
add column aws_region varchar(32) default 'us-east-1';

Will it update all rows? Or is default value is just a value lookup if value is not present?

Upvotes: 1

Views: 751

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562330

It's easy to test this out. If you add a column to a table that has one or more rows, it sets the new column to the default.

mysql> create table foo (id int primary key);

mysql> insert into foo values (123);

mysql> alter table foo
    -> add column aws_region varchar(32) default 'us-east-1';

mysql> select * from foo;
+-----+------------+
| id  | aws_region |
+-----+------------+
| 123 | us-east-1  |
+-----+------------+

If you change the definition of the column, existing rows are not changed. The value in each row is set when you create the row, not when you query it.

mysql> alter table foo 
    -> modify column aws_region varchar(32) default 'us-west-2';

mysql> select * from foo;
+-----+------------+
| id  | aws_region |
+-----+------------+
| 123 | us-east-1  |
+-----+------------+

New rows you insert after the default has been changed use the new default. The old rows still have the old value.

mysql> insert into foo (id) values (456);

mysql> select * from foo;
+-----+------------+
| id  | aws_region |
+-----+------------+
| 123 | us-east-1  |
| 456 | us-west-2  |
+-----+------------+

This means that the default value is copied into the row at the time the row is inserted. There is only default at a time, so if the row contained a pointer to the default, and then we changed the default, old rows that used the default would appear to change retroactively, as they now point to the new default value.

They don't do that, as I showed above. Old rows still contain the default that was in effect at the time the row was created. The default value was copied into the row.

Upvotes: 3

xiaoqian
xiaoqian

Reputation: 71

The data which alter table before will not change. Es: if is null after alter it is also null

Upvotes: 0

Related Questions