MySQL 更新 IF (ROW_COUNT() = 0)

Question

我有这个 SQL:

UPDATE gcd_data 
SET post_title = 'Hello World', 
    post_content = 'How Are You?',  
    post_date_gmt = '', 
    post_modified_gmt = '',
    post_url = 'www.google.com', 
    post_type = 'product'
WHERE gcd_id='1024'
IF (ROW_COUNT() = 0)
INSERT INTO gcd_data (gcd_id, post_title, post_content, post_date_gmt, 
                   post_modified_gmt, post_url, post_type) 
VALUES ('1024', 'Hello World', 'How Are You?', '', '', 'www.google.com', 'product')

它给我这样的错误:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF (ROW_COUNT() = 0)
INSERT INTO gcd_data (gcd_id, post_title, post_content, pos' at line 9

我阅读了有关 IF 语句的 MySQL 文档，但没有发现任何问题。那么，如何纠正这个问题？

Answer 1

我认为你应该做insert on duplicate key update:

INSERT INTO gcd_data (gcd_id, post_title, post_content, post_date_gmt, 
                      post_modified_gmt, post_url, post_type) 
    VALUES (1024, 'Hello World', 'How Are You?', '', '', 'www.google.com', 'product')
    ON DUPLICATE KEY UPDATE 
        post_title = 'Hello World', 
        post_content = 'How Are You?',  
        post_date_gmt = '', 
        post_modified_gmt = '',
        post_url = 'www.google.com', 
        post_type = 'product';

为此，您需要在 gcd_data(gcd_id) 上有一个唯一索引。如果它被声明为主键(可能是)，那么你会自动得到它。如果不是:

create unique index idx_gcd_data_gcd_id on gcd_data(gcd_id);

如果该字段不是唯一的，那么您真的应该重新考虑您的数据模型。

通常，多次重复这些值被认为是不好的做法。你也可以这样写:

INSERT INTO gcd_data (gcd_id, post_title, post_content, post_date_gmt, 
                      post_modified_gmt, post_url, post_type) 
    VALUES (1024, 'Hello World', 'How Are You?', '', '', 'www.google.com', 'product')
    ON DUPLICATE KEY UPDATE 
        post_title = VALUES(post_title), 
        post_content = VALUES(post_content,  
        post_date_gmt = VALUES(post_date_gmt), 
        post_modified_gmt = VALUES(post_modified_gmt),
        post_url = VALUES(post_url), 
        post_type = VALUES(post_type);