mysql - 将时间戳列添加到通过查询创建的表中

我想从mysql查询创建表，但是添加一个带有时间戳的新列，我将在其中捕获创建日期。
我的问题是：

CREATE TABLE archive
  AS ( SELECT a.* FROM
        (
            (
              SELECT p.projectNo, usr.staff_id, usr.longname, usr.username, p.title, CONCAT(upr.process, " (", upr.role, ")" ) AS process
              FROM project p
                INNER JOIN user_project upr ON p.projectNo = upr.projectNo
                    INNER JOIN user usr ON upr.username = usr.username
            )
        ) AS a
        ORDER BY a.projectNo);

如何添加时间戳？

最佳答案

您可以选择NOW()作为创建时间戳的额外列，例如。

CREATE TABLE archive AS
(
    SELECT a.*
    FROM
    (
        (
            SELECT p.projectNo,
                   usr.staff_id,
                   usr.longname,
                   usr.username,
                   p.title,
                   CONCAT(upr.process, " (", upr.role, ")" ) AS process,
                   NOW() AS creation_time     -- added column
            FROM project p
            INNER JOIN user_project upr
                ON p.projectNo = upr.projectNo
            INNER JOIN user usr
                ON upr.username = usr.username
        )
    ) AS a
    ORDER BY a.projectNo
);

关于mysql - 将时间戳列添加到通过查询创建的表中，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41866589/