使用现有字段值的MySQL修改表

I am working with a database that has a table called date, which contains a separate field for day, month, year. Clearly this is not ideal when I am trying to run comparisons, etc. I am wondering is it possible for me to add a DateTime field to each row of this table and insert a concatenated string into the new field from the existing day, month, year fields.

我正在处理一个数据库，该数据库有一个名为date的表，其中包含一天、一个月、一年的独立字段。显然，当我尝试运行比较时，这并不理想。我想知道是否可能向该表的每一行添加一个DateTime字段，并从现有的日期、月份和年份字段中插入一个连接字符串到新字段中。

I am quite sure its possible, I'm just wondering if anyone might be able to point me in the right direction on how to achieve this?

我很确定这是可能的，我只是想知道是否有人能给我指出正确的方向来实现这一点?

Below is the current date table: (i know)

下面是当前日期表:(我知道)

CREATE TABLE IF NOT EXISTS `date` (
  `deposition_id` varchar(11) NOT NULL default '',
  `day` int(2) default NULL,
  `month` int(2) default NULL,
  `year` int(4) default NULL,
  PRIMARY KEY  (`deposition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

3 个解决方案

#1

first use alter table query:

第一次使用alter table查询:

alter table date add column datetimefield datetime NOT Null

添加列datetimefield datetime(不为空)

then

然后

use update query with self join on date and update datetimefield with concat on date,month, year column values.

在日期、月、年的列值上用concat更新datetimefield，并在日期和更新日期字段中使用update查询。

#2

Try this (untested) -

试试这个(未测试)

UPDATE date d SET d.datetime = (SELECT CONCAT('-','year','month','day') from date d1 where d1.id = d.id);

#3

What is the problem, I don't understand? Alter the table, add new DATE column and then populate it with a string "yyyy-mm-dd" using CONCAT mysql function or whatever.

有什么问题，我不明白?修改表，添加新的日期列，然后使用CONCAT mysql函数或其他方法填充一个字符串“yyyy-mm-dd”。

#1