首页 \ 问答 \ 默认值如何在数据库中内部工作?(How does default value internally work in Database?)

默认值如何在数据库中内部工作?(How does default value internally work in Database?)

默认值如何在数据库中内部工作?

如果我改变一个表:

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

它会更新所有行吗? 或者,如果值不存在,默认值只是值查找?


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?


原文:https://stackoverflow.com/questions/44854543
更新时间:2019-12-07 10:29

最满意答案

这很容易测试出来。 如果向具有一行或多行的表添加列,则会将新列设置为默认值。

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  |
+-----+------------+

如果更改列的定义,则不会更改现有行。 创建行时设置每行中的值,而不是在查询时设置。

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  |
+-----+------------+

更改默认值后插入的新行使用新的默认值。 旧行仍具有旧值。

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

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

这意味着在插入行时将默认值复制到行中。 一次只有默认值,因此如果行包含指向默认值的指针,然后我们更改了默认值,则使用默认值的旧行似乎会追溯更改,因为它们现在指向新的默认值。

正如我在上面所展示的那样,他们不这样做。 旧行仍包含在创建行时生效的默认值。 默认值已复制到行中。


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.

2017-07-05

相关文章

更多

最新问答

更多
  • 这个listArray是如何填充的?(How is this listArray populated?)
  • iOS 7上的CTSubscriber(以及如何使用它)是什么?(What's CTSubscriber (and how to use it) on iOS 7?)
  • 手动创建VisualStudio 2012项目文件(Manually creating VisualStudio 2012 project file)
  • 删除不适用于JSP中使用for循环的每个id(Deletion not working for every id using for loop in JSP)
  • 如何从std :: filesystem :: path中删除引号(How to remove quotation marks from std::filesystem::path)
  • 验证多个控制器方法的URL路径(Validate URL path for several controller methods)
  • 如何在datarow []中的列中找到最大值?(How to find max value in a column in a datarow[] ?)
  • 如何使用预定义文本替换来自数据库的部分结果(How do I replace part of result coming from Database with predefined text)
  • Selenium Java注入了新的Javascript函数(Selenium Java inject new Javascript function)
  • 使用.on的多个下拉菜单选择文本仅适用于第一个下拉列表(Multiple Dropdowns Menu Selection text using .on works only on first dropdown)
  • 快速将黄土曲线添加到大型数据集图中的方法(Quick way to add loess curve to large data set graph)
  • FilteringSelect in mvc(FilteringSelect in mvc)
  • 在Delphi XE2中开发Mac或iOS应用程序需要哪些硬件/软件?(What hardware/software is necessary to develop Mac or iOS apps in Delphi XE2?)
  • 在原型的构造函数中初始化属性时获取“未定义”(Getting 'undefined' when a property is initialized in the constructor of a prototype)
  • 通过越狱加载的应用程序的Documents文件夹位置(Location of Documents folder for an app loaded via jailbreak)
  • 在OpenGL中使用可编程和固定管道功能(Using both programmable and fixed pipeline functionality in OpenGL)
  • 将任何用户输入重定向到单独的底层程序(redirect any user input to a separate underlying program)
  • 编辑文本不能正常工作android(Edit texts not working properly android)
  • “user_denied”Facebook应用页面上的Facebook用户区域设置(Facebook user locale on “user_denied” facebook app page)
  • 在大图像中找到小的部分透明图像的坐标(find coordinates of small partially-transparent image within a large image)
  • 我如何在cakephp 3.1中获得完整的相对路径?(How i can get full relative path of image in cakephp 3.1?)
  • 如何保存拖动标记的新本地化?(How to save new localization of dragged marker?)
  • MySQL UPDATE vs INSERT和DELETE(MySQL UPDATE vs INSERT and DELETE)
  • 在执行查询之前,在SQLAlchemy模型中将datetime转换为unix时间戳?(Convert datetime to unix timestamp in SQLAlchemy model before executing query?)
  • OpenCL与OpenGL互操作的优势(Advantage of OpenCL interoperability with OpenGL)
  • 如何解析用点和等分隔的数据然后添加到listview(How to parsing data from delimited with dot and equal then add to listview)
  • 带调试输出的X3解析器段错误(BOOST_SPIRIT_X3_DEBUG)(X3 parser segfaults with debug output (BOOST_SPIRIT_X3_DEBUG))
  • 将文件夹名称添加到fgrep结果(Add folder name to fgrep result)
  • 在MySQL中加载一个表是非常慢的(Loading one table in MySQL is ridiculously slow)
  • 如何将JSON放入PHP变量?(How do I put JSON into a PHP Variable?)