mysql - 从连接表插入表，更新的表有额外的列

Question

我有一个具有这种结构的表:

CREATE TABLE `jos_cpnllist` (
    `MSTMLSNO` int(7),
    `CLTIME` TIMESTAMP NULL,
    PRIMARY KEY (`MSTMLSNO`))
    ENGINE=MyISAM 
    SELECT * FROM jos_mls AS mls
        INNER JOIN jos_activeagents AS active ON mls.MSTLISTBRD = active.AGENTUID
        INNER JOIN jos_agents AS agents ON active.AGENTUID = agents.AGTBRDIDMM;

然后我想定期更新这个表中的数据:

INSERT INTO jos_cpnllist
    SELECT * FROM jos_mls AS mls
        INNER JOIN jos_activeagents AS active ON mls.MSTLISTBRD = active.AGENTUID
        INNER JOIN jos_agents AS agents ON active.AGENTUID = agents.AGTBRDIDMM;

添加的时间戳列在我进行更新时给我带来了麻烦:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

有没有办法在不特别列出 SELECT 中的列的情况下解决这个问题？告诉它忽略 CLTIME 时间戳列？

注意: 大约有 75 列数据，所以我想避免列出所有这些。 MSTMLSNO 列已存在于 jos_mls 表中，因此它不会引发错误。 CLTIME 是大 INNER JOIN 语句中唯一不存在的列。

Answer 1

使用插入时，总是明确指定列:

INSERT INTO jos_cpnllist(MSTMLSNO, CLTIME)
    SELECT <columnX> as MSTMLSNO, <columnY> as CLTIME
    FROM jos_mls AS mls
        INNER JOIN jos_activeagents AS active ON mls.MSTLISTBRD = active.AGENTUID
        INNER JOIN jos_agents AS agents ON active.AGENTUID = agents.AGTBRDIDMM;

列出 75 列并不难。您可以从 INFORMATION_SCHEMA.columns 获取列表并在 Excel 中设置格式。