MySQL insert文法注意事項(ON DUPLICATE KEY UPDATE )

2014年10月3日

1.INSERT説明
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,…)]
VALUES ({expr | DEFAULT},…),(…),…
[ ON DUPLICATE KEY UPDATE col_name=expr, … ]
または
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, …
[ ON DUPLICATE KEY UPDATE col_name=expr, … ]
または
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,…)]
SELECT …
[ ON DUPLICATE KEY UPDATE col_name=expr, … ]

2.使い方
2.1 IGNORE
insert ignore into tb(…) value(…)
2.2 ON DUPLICATE KEY UPDATE
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
mysql> UPDATE table SET c=c+1 WHERE a=1;
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
mysql> INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;