mysql - 我收到重复的列名，但基于值。当我更改值时，没有错误

Question

我正在尝试使用此查询，但是当不同列中的值相同时，我收到此错误：

1060 - 列名“123”重复

例如这里：

INSERT INTO chiro(in_out,chirocov,chirocov2,chiroded,chiromet,
                    chirocovp,chirooop,chirooopmet,chirooopcp, 
                    chirovisit,chirouse,chiromax,chirodedapply,
                    chironum1,chironum2) 
    SELECT * FROM (SELECT 'in', 'no','individual','123','123','20', 
                            '213','21243','10','14','5','2000','yes', 
                            '0','1') AS tmp  
    WHERE NOT EXISTS (SELECT in_out,chirocov,chirocov2,
                            chiroded,chiromet,chirocovp,chirooop,
                            chirooopmet,chirooopcp, chirovisit,chirouse,
                            chiromax,chirodedapply,chironum1,chironum2 
                        FROM chiro 
                        WHERE chirocov='no' 
                        AND chirocov2='individual' 
                        AND chiroded='123' 
                        AND chiromet='123' 
                        AND chirocovp='20' 
                        AND chirooop='213' 
                        AND chirooopmet='213' 
                        AND chirooopcp='10' 
                        AND chirovisit='14' 
                        AND chirouse='5' 
                        AND chiromax='2000' 
                        AND chirodedapply='yes' 
                        AND chironum1='0' 
                        AND chironum2='1') 
    LIMIT 1

但是当我更改值时，不会有任何错误。例如：

INSERT INTO chiro(in_out,chirocov,chirocov2,chiroded,
                    chiromet,chirocovp,chirooop,chirooopmet,
                    chirooopcp, chirovisit,chirouse,chiromax,
                    chirodedapply,chironum1,chironum2) 
        SELECT * FROM (SELECT 'in', 'no','individual','123','231','20', 
                                '213','21243','10','14','5','2000',
                                'yes', '0','1') AS tmp 
                        WHERE NOT EXISTS (SELECT in_out,chirocov,chirocov2,
                                chiroded,chiromet,chirocovp,chirooop,
                                chirooopmet,chirooopcp, chirovisit,chirouse,
                                chiromax,chirodedapply,chironum1,chironum2 
                        FROM chiro 
                        WHERE chirocov='no' 
                        AND chirocov2='individual' 
                        AND chiroded='123' 
                        AND chiromet='123' 
                        AND chirocovp='20' 
                        AND chirooop='213' 
                        AND chirooopmet='213' 
                        AND chirooopcp='10' 
                        AND chirovisit='14' 
                        AND chirouse='5' 
                        AND chiromax='2000' 
                        AND chirodedapply='yes' 
                        AND chironum1='0' 
                        AND chironum2='1') 
    LIMIT 1 

你能帮助我，让我知道我做错了什么吗？

标签： mysql

Answer 1

只需从以下位置移除外部SELECT：

SELECT * FROM (SELECT 'in', 'no','individual','123','123','20', 
               '213','21243','10','14','5','2000','yes', 
               '0','1') AS tmp 

因为它从内部检索所有未命名的列，其select名称是它们的值，所以有 2 列具有相同的名称123。
查看问题的简化演示。
用这个：

INSERT INTO chiro(
   in_out, chirocov, chirocov2, chiroded, chiromet, chirocovp, chirooop, chirooopmet, chirooopcp,  
   chirovisit, chirouse, chiromax, chirodedapply, chironum1, chironum2
) 
SELECT 'in',  'no', 'individual', '123', '123', '20',  '213', '21243', '10', '14', '5', '2000', 'yes',  '0', '1'
WHERE NOT EXISTS(
  SELECT in_out, chirocov, chirocov2, chiroded, chiromet, chirocovp, chirooop, chirooopmet, chirooopcp,    
         chirovisit, chirouse, chiromax, chirodedapply, chironum1, chironum2 
  FROM chiro 
  WHERE chirocov='no' AND chirocov2='individual' AND chiroded='123' AND chiromet='123' AND chirocovp='20' 
  AND chirooop='213' AND chirooopmet='213' AND chirooopcp='10' AND chirovisit='14' AND chirouse='5' 
  AND chiromax='2000' AND chirodedapply='yes' AND chironum1='0' AND chironum2='1'
) 

也不LIMIT 1是必须的。