拆分日期并在mysql的单独列中插入单个字段(splitting date and inserting individual field in separate column in mysql)

拆分日期并在mysql的单独列中插入单个字段(splitting date and inserting individual field in separate column in mysql)

我直接通过phpmyadmin创建了一个日期表，我想将日期（yyyy：mm：dd）分成单独的日，月和年，并插入到单独的字段中。 任何人都可以帮助我直接在phpmyadmin中使用的查询来执行上述工作吗？

i have created a date table directly through phpmyadmin and i want to split the date(yyyy:mm:dd) into individual day, month and year and insert into separate field. Can anybody help me in the query used directly in phpmyadmin to perform above work?

最满意答案

尝试：

UPDATE `table` SET `year` = YEAR(`date`), `month` = DATE_FORMAT(`date`, '%m'), `day` = DATE_FORMAT(`date`, '%d')

我使用了DATE_FORMAT()而不是MONTH()和DAY()因为后两个删除了前导零。

Try:

UPDATE `table` SET `year` = YEAR(`date`), `month` = DATE_FORMAT(`date`, '%m'), `day` = DATE_FORMAT(`date`, '%d')

I've used DATE_FORMAT() rather than MONTH() and DAY() as the latter two remove leading zeros.