英文:
How to set the default value for a DateTime column in MySQL workbench?
问题
我有一个名为CreatedDate的列，我想为该列设置以下格式的默认值。
默认值：yyyy-mm-dd 00:00:00.000
我尝试了多种解决方案，比如我使用了DATETIME数据类型、TIMESTAMP，但它们都没有接受默认值。
请问是否有人可以分享您对此的想法？
谢谢。
英文:
I have a column CreatedDate and I want to set the below default value in the below format for this column.
Default value:- yyyy-mm-dd 00:00:00.000
I have tried multiple solutions like I have used DATETIME datatype, TIMESTAMP but it is not taking the default value.
Could someone please share your thoughts on this?
Thanks,
答案1
得分: 2
展示的格式是默认的日期时间格式，如果你使用毫秒精度定义列，它将采用这个格式。
演示：
mysql> create table mytable (id serial primary key, ts datetime(3));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into mytable set ts = now();
Query OK, 1 row affected (0.01 sec)
mysql> select * from mytable;
+----+-------------------------+
| id | ts |
+----+-------------------------+
| 1 | 2023-03-06 09:15:34.000 |
+----+-------------------------+
无法更改日期时间的默认格式。日期时间并非以字符串形式存储，而是以二进制值存储。你必须使用DATE_FORMAT()函数来显示不同的格式。
英文:
The format you show is the default datetime format if you define the column with millisecond precision.
Demo:
mysql> create table mytable (id serial primary key, ts datetime(3));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into mytable set ts = now();
Query OK, 1 row affected (0.01 sec)
mysql> select * from mytable;
+----+-------------------------+
| id | ts |
+----+-------------------------+
| 1 | 2023-03-06 09:15:34.000 |
+----+-------------------------+
There is no way to change the default format of a datetime. Datetimes are not stored as a string, they are stored as a binary value. You must use the DATE_FORMAT() function to display a different format.
答案2
得分: 1
如果您只想将默认值设置为特定的硬编码值，可以使用以下代码：
ALTER TABLE tableName ALTER CreatedDate SET DEFAULT "2017-06-28 00:00:00.000";
有关更多信息，请参阅ALTER TABLE Statement文档。
英文:
If you just want to set the default value to a specific hard-coded value, you can use the following
ALTER TABLE tableName ALTER CreatedDate SET DEFAULT "2017-06-28 00:00:00.000";
See ALTER TABLE Statement docs
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论