客户给了两个CSV文件，里面记录了几千条EMAIL记录，要我导入到网站里。

由于EMAIL字段是唯一键，所以不能单纯的用 INSRT 就完事了。

如果用PHP一条记录一条记录的去检查，那可太浪费了。

原来做 ORACLE 的时候，有个 MARGE INTO 语法(Oracle  基本全给忘了！)

 

MySQL下，我记得有个 REPLACE什么的在，查了查，如下：

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERT Syntax”.

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL — that either inserts or updates — see Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.


不过，这个没细看，我用了另外一个：

INSERT ... ON DUPLICATE KEY UPDATE Syntax

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

下面是示例：
 

INSERT INTO jos_acajoom_subscribers (name,email,receive_html,confirmed) VALUES
('ANIL','a.malhotra@xxx',1,1),
('Anna','a.vasquez@xxx,1,1),
('Aaron','aaron.evans@xxx',1,1),
ON DUPLICATE KEY UPDATE
confirmed = 1;

 

INSERT INTO jos_acajoom_subscribers (name,email,receive_html,confirmed) VALUES
('ANIL','a.malhotra@xxx',1,1),
('An','a.millard@xxx',1,1),
('Anna','a.vasquez@xxx',1,1)

ON DUPLICATE KEY UPDATE
confirmed = 1;