Ò»¡¢INSERTºÍREPLACE
INSERTºÍREPLACEÓï¾äµÄ¹¦Äܶ¼ÊÇÏò±íÖвåÈëеÄÊý¾Ý¡£ÕâÁ½ÌõÓï¾äµÄÓï·¨ÀàËÆ¡£ËüÃǵÄÖ÷񻂿±ðÊÇÈçºÎ´¦ÀíÖØ¸´µÄÊý¾Ý¡£
1. INSERTµÄÒ»°ãÓ÷¨
MySQLÖеÄINSERTÓï¾äºÍ±ê×¼µÄINSERT²»Ì«Ò»Ñù£¬ÔÚ±ê×¼µÄSQLÓï¾äÖУ¬Ò»´Î²åÈëÒ»Ìõ¼Ç¼µÄINSERTÓï¾äÖ»ÓÐÒ»ÖÖÐÎʽ¡£
INSERT INTO tablename(ÁÐÃû¡) VALUES(ÁÐÖµ);
¶øÔÚMySQLÖл¹ÓÐÁíÍâÒ»ÖÖÐÎʽ¡£
INSERT INTO tablename SET column_name1 = value1, column_name2 = value2£¬¡; |
µÚÒ»ÖÖ·½·¨½«ÁÐÃûºÍÁÐÖµ·Ö¿ªÁË£¬ÔÚʹÓÃʱ£¬ÁÐÃû±ØÐëºÍÁÐÖµµÄÊýÒ»Ö¡£ÈçÏÂÃæµÄÓï¾äÏòusers±íÖвåÈëÁËÒ»Ìõ¼Ç¼£º
INSERT INTO users(id, name, age) VALUES(123, 'Ò¦Ã÷', 25); |
µÚ¶þÖÖ·½·¨ÔÊÐíÁÐÃûºÍÁÐÖµ³É¶Ô³öÏÖºÍʹÓã¬ÈçÏÂÃæµÄÓï¾ä½«²úÉúÖÐÑùµÄЧ¹û¡£
INSERT INTO users SET id = 123, name = 'Ò¦Ã÷', age = 25; |
Èç¹ûʹÓÃÁËSET·½Ê½£¬±ØÐëÖÁÉÙΪһÁи³Öµ¡£Èç¹ûijһ¸ö×Ö¶ÎʹÓÃÁËʡȱֵ(ÈçĬÈÏ»ò×ÔÔöÖµ)£¬ÕâÁ½ÖÖ·½·¨¶¼¿ÉÒÔÊ¡ÂÔÕâЩ×ֶΡ£Èçid×Ö¶ÎÉÏʹÓÃÁË×ÔÔöÖµ£¬ÉÏÃæÁ½ÌõÓï¾ä¿ÉÒÔд³ÉÈçÏÂÐÎʽ£º
INSERT INTO users (name, age) VALUES('Ò¦Ã÷',25);
INSERT INTO uses SET name = 'Ò¦Ã÷', age = 25; |
MySQLÔÚVALUESÉÏÒ²×öÁËЩ±ä»¯¡£Èç¹ûVALUESÖÐʲô¶¼²»Ð´£¬ÄÇMySQL½«Ê¹ÓñíÖÐÿһÁеÄĬÈÏÖµÀ´²åÈëмǼ¡£
INSERT INTO users () VALUES();
Èç¹û±íÃûºóʲô¶¼²»Ð´£¬¾Í±íʾÏò±íÖÐËùÓеÄ×ֶθ³Öµ¡£Ê¹ÓÃÕâÖÖ·½Ê½£¬²»½öÔÚVALUESÖеÄÖµÒªºÍÁÐÊýÒ»Ö£¬¶øÇÒ˳Ðò²»Äܵߵ¹¡£
INSERT INTO users VALUES(123, 'Ò¦Ã÷', 25); |
Èç¹û½«INSERTÓï¾äд³ÉÈçÏÂÐÎʽMySQL½«»á±¨´í¡£
INSERT INTO users VALUES('Ò¦Ã÷',25); |
2. ʹÓÃINSERT²åÈë¶àÌõ¼Ç¼
¿´µ½Õâ¸ö±êÌâÒ²Ðí´ó¼Ò»áÎÊ£¬ÕâÓÐʲôºÃ˵µÄ£¬µ÷Óöà´ÎINSERTÓï¾ä²»¾Í¿ÉÒÔ²åÈë¶àÌõ¼Ç¼ÁËÂð!µ«Ê¹ÓÃÕâÖÖ·½·¨ÒªÔö¼Ó·þÎñÆ÷µÄ¸ººÉ£¬ÒòΪ£¬Ö´ÐÐÿһ´ÎSQL·þÎñÆ÷¶¼ÒªÍ¬Ñù¶ÔSQL½øÐзÖÎö¡¢ÓÅ»¯µÈ²Ù×÷¡£ÐÒºÃMySQLÌṩÁËÁíÒ»ÖÖ½â¾ö·½°¸£¬¾ÍÊÇʹÓÃÒ»ÌõINSERTÓï¾äÀ´²åÈë¶àÌõ¼Ç¼¡£Õâ²¢²»ÊDZê×¼µÄSQLÓï·¨£¬Òò´ËÖ»ÄÜÔÚMySQLÖÐʹÓá£
INSERT INTO users(name, age) VALUES('Ò¦Ã÷', 25), ('±È¶û.¸Ç´Ä', 50), ('»ðÐÇÈË', 600); |
ÉÏÃæµÄINSERT Óï¾äÏòusers±íÖÐÁ¬Ðø²åÈëÁË3Ìõ¼Ç¼¡£ÖµµÃ×¢ÒâµÄÊÇ£¬ÉÏÃæµÄINSERTÓï¾äÖеÄVALUESºó±ØÐëÿһÌõ¼Ç¼µÄÖµ·Åµ½Ò»¶Ô(¡)ÖУ¬ÖмäʹÓÃ","·Ö¸î¡£¼ÙÉèÓÐÒ»¸ö±ítable1
CREATE TABLE table1(n INT); |
Èç¹ûÒªÏòtable1ÖвåÈë5Ìõ¼Ç¼£¬ÏÂÃæÐ´·¨ÊÇ´íÎóµÄ£º
INSERT INTO table1 (i) VALUES(1,2,3,4,5); |
MySQL½«»áÅ׳öÏÂÃæµÄ´íÎó
ERROR 1136: Column count doesn't match value count at row 1 |
¶øÕýÈ·µÄд·¨Ó¦¸ÃÊÇÕâÑù£º
INSERT INTO table1(i) VALUES(1),(2),(3),(4),(5); |
µ±È»£¬ÕâÖÖд·¨Ò²¿ÉÒÔÊ¡ÂÔÁÐÃû£¬ÕâÑùÿһ¶ÔÀ¨ºÅÀïµÄÖµµÄÊýÄ¿±ØÐëÒ»Ö£¬¶øÇÒÕâ¸öÊýÄ¿±ØÐëºÍÁÐÊýÒ»Ö¡£È磺
INSERT INTO table1 VALUES(1),(2),(3),(4),(5); |
3. REPLACEÓï¾ä
ÎÒÃÇÔÚʹÓÃÊý¾Ý¿âʱ¿ÉÄܻᾳ£Óöµ½ÕâÖÖÇé¿ö¡£Èç¹ûÒ»¸ö±íÔÚÒ»¸ö×Ö¶ÎÉϽ¨Á¢ÁËΨһË÷Òý£¬µ±ÎÒÃÇÔÙÏòÕâ¸ö±íÖÐʹÓÃÒѾ´æÔڵļüÖµ²åÈëÒ»Ìõ¼Ç¼£¬Äǽ«»áÅ׳öÒ»¸öÖ÷¼ü³åÍ»µÄ´íÎó¡£µ±È»£¬ÎÒÃÇ¿ÉÄÜÏëÓÃмǼµÄÖµÀ´¸²¸ÇÔÀ´µÄ¼Ç¼ֵ¡£Èç¹ûʹÓô«Í³µÄ×ö·¨£¬±ØÐëÏÈʹÓà DELETEÓï¾äɾ³ýÔÏȵļǼ£¬È»ºóÔÙʹÓÃINSERT²åÈëеļǼ¡£¶øÔÚMySQLÖÐΪÎÒÃÇÌṩÁËÒ»ÖÖеĽâ¾ö·½°¸£¬Õâ¾ÍÊÇREPLACEÓï¾ä¡£Ê¹Óà REPLACE²åÈëÒ»Ìõ¼Ç¼ʱ£¬Èç¹û²»Öظ´£¬REPLACE¾ÍºÍINSERTµÄ¹¦ÄÜÒ»Ñù£¬Èç¹ûÓÐÖØ¸´¼Ç¼£¬REPLACE¾ÍʹÓÃмǼµÄÖµÀ´Ìæ»»ÔÀ´µÄ¼Ç¼ֵ¡£
ʹÓÃREPLACEµÄ×î´óºÃ´¦¾ÍÊÇ¿ÉÒÔ½«DELETEºÍINSERTºÏ¶þΪһ£¬ÐγÉÒ»¸öÔ×Ó²Ù×÷¡£ÕâÑù¾Í¿ÉÒÔ²»±Ø¿¼ÂÇÔÚͬʱʹÓÃDELETEºÍINSERTʱÌí¼ÓÊÂÎñµÈ¸´ÔÓ²Ù×÷ÁË¡£
ÔÚʹÓÃREPLACEʱ£¬±íÖбØÐëÓÐΨһË÷Òý£¬¶øÇÒÕâ¸öË÷ÒýËùÔÚµÄ×ֶβ»ÄÜÔÊÐí¿ÕÖµ£¬·ñÔòREPLACE¾ÍºÍINSERTÍêȫһÑùµÄ¡£
ÔÚÖ´ÐÐREPLACEºó£¬ÏµÍ³·µ»ØÁËËùÓ°ÏìµÄÐÐÊý£¬Èç¹û·µ»Ø1£¬ËµÃ÷ÔÚ±íÖв¢Ã»ÓÐÖØ¸´µÄ¼Ç¼£¬Èç¹û·µ»Ø2£¬ËµÃ÷ÓÐÒ»ÌõÖØ¸´¼Ç¼£¬ÏµÍ³×Ô¶¯Ïȵ÷ÓÃÁË DELETEɾ³ýÕâÌõ¼Ç¼£¬È»ºóÔټǼÓÃINSERTÀ´²åÈëÕâÌõ¼Ç¼¡£Èç¹û·µ»ØµÄÖµ´óÓÚ2£¬ÄÇ˵Ã÷Óжà¸öΨһË÷Òý£¬ÓжàÌõ¼Ç¼±»É¾³ýºÍ²åÈë¡£
REPLACEµÄÓï·¨ºÍINSERT·Ç³£µÄÏàËÆ£¬ÈçÏÂÃæµÄREPLACEÓï¾äÊDzåÈë»ò¸üÐÂÒ»Ìõ¼Ç¼¡£
REPLACE INTO users (id,name,age) VALUES(123, 'ÕÔ±¾É½', 50); |
²åÈë¶àÌõ¼Ç¼£º
REPLACE INTO users(id, name, age) VALUES(123, 'ÕÔ±¾É½', 50), (134,'Mary',15);
REPLACEÒ²¿ÉÒÔʹÓÃSETÓï¾ä
REPLACE INTO users SET id = 123, name = 'ÕÔ±¾É½', age = 50; |
ÉÏÃæÔøÌáµ½REPLACE¿ÉÄÜÓ°Ïì3ÌõÒÔÉϵļǼ£¬ÕâÊÇÒòΪÔÚ±íÖÐÓг¬¹ýÒ»¸öµÄΨһË÷Òý¡£ÔÚÕâÖÖÇé¿öÏ£¬REPLACE½«¿¼ÂÇÿһ¸öΨһË÷Òý£¬²¢¶Ôÿһ¸öË÷Òý¶ÔÓ¦µÄÖØ¸´¼Ç¼¶¼É¾³ý£¬È»ºó²åÈëÕâÌõмǼ¡£¼ÙÉèÓÐÒ»¸ötable1±í£¬ÓÐ3¸ö×Ö¶Îa, b, c¡£ËüÃǶ¼ÓÐÒ»¸öΨһË÷Òý¡£
CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE); |
¼ÙÉètable1ÖÐÒѾÓÐÁË3Ìõ¼Ç¼
a b c
1 1 1
2 2 2
3 3 3
ÏÂÃæÎÒÃÇʹÓÃREPLACEÓï¾äÏòtable1ÖвåÈëÒ»Ìõ¼Ç¼¡£
REPLACE INTO table1(a, b, c) VALUES(1,2,3); |
·µ»ØµÄ½á¹ûÈçÏÂ
Query OK, 4 rows affected (0.00 sec) |
ÔÚtable1ÖеļǼÈçÏÂ
a b c
1 2 3
ÎÒÃÇ¿ÉÒÔ¿´µ½£¬REPLACE½«ÔÏȵÄ3Ìõ¼Ç¼¶¼É¾³ýÁË£¬È»ºó½«(1, 2, 3)²åÈë¡£
¶þ¡¢UPDATE
UPDATEµÄ¹¦ÄÜÊǸüбíÖеÄÊý¾Ý¡£ÕâµÄÓï·¨ºÍINSERTµÄµÚ¶þÖÖÓ÷¨ÏàËÆ¡£±ØÐëÌṩ±íÃûÒÔ¼°SET±í´ïʽ£¬ÔÚºóÃæ¿ÉÒÔ¼ÓWHEREÒÔÏÞÖÆ¸üеļǼ·¶Î§¡£
UPDATE table_anem SET column_name1 = value1, column_name2 = value2, WHERE ; |
ÈçÏÂÃæµÄÓï¾ä½«users±íÖÐidµÈÓÚ123µÄ¼Ç¼µÄage¸ÄΪ24
UPDATE users SET age = 24 WHERE id = 123; |
ͬÑù£¬¿ÉÒÔʹÓÃUPDATE¸üжà¸ö×ֶεÄÖµ
UPDATE users SET age = 24, name = 'Mike' WHERE id = 123; |
ÉÏÃæµÄUPDATEÓï¾äͨ¹ýWHEREÖ¸¶¨Ò»¸öÌõ¼þ£¬·ñÔò£¬UPDATE½«¸üбíÖеÄËùÓмǼµÄÖµ¡£
ÔÚʹÓÃUPDATE¸üмǼʱ£¬Èç¹û±»¸üеÄ×ֶεÄÀàÐͺÍËù¸³µÄÖµ²»Æ¥Åäʱ£¬MySQL½«Õâ¸öֵת»»ÎªÏàÓ¦ÀàÐ͵ÄÖµ¡£Èç¹ûÕâ¸ö×Ö¶ÎÊÇÊýÖµÀàÐÍ£¬¶øÇÒËù¸³Öµ³¬¹ýÁËÕâ¸öÊý¾ÝÀàÐ͵Ä×î´ó·¶Î§£¬ÄÇôMySQL¾Í½«Õâ¸öֵת»»ÎªÕâ¸ö·¶Î§×î´ó»ò×îСֵ¡£Èç¹û×Ö·û´®Ì«³¤£¬MySQL¾Í½«¶àÓàµÄ×Ö·û´®½ØÈ¥¡£Èç¹ûÉèÖ÷ǿÕ×Ö¶ÎΪ¿Õ£¬ÄÇô½«Õâ¸ö×Ö¶ÎÉèÖÃΪËüÃǵÄĬÈÏÖµ£¬Êý×ÖµÄĬÈÏÖµÊÇ0£¬×Ö·û´®µÄĬÈÏÖµÊǿմ®(²»ÊÇnull£¬ÊÇ"")¡£
ÓÐÁ½ÖÖÇé¿öUPDATE²»»á¶ÔÓ°Ïì±íÖеÄÊý¾Ý¡£
1. µ±WHEREÖеÄÌõ¼þÔÚ±íÖÐûÓмǼºÍËüÆ¥Åäʱ¡£
2. µ±ÎÒÃǽ«Í¬ÑùµÄÖµ¸³¸øÄ³¸ö×Ö¶Îʱ£¬È罫×Ö¶Îabc¸³Îª'123'£¬¶øabcµÄÔÖµ¾ÍÊÇ'123'¡£
ºÍINSERT¡¢REPLACEÒ»Ñù£¬UPDATEÒ²·µ»ØËù¸üеļǼÊý¡£µ«ÕâЩ¼Ç¼Êý²¢²»°üÀ¨Âú×ãWHEREÌõ¼þµÄ£¬µ«È´Î´±»¸üеļǼ¡£ÈçÏÂͬµÄUPDATEÓï¾ä¾Íδ¸üÐÂÈκμǼ¡£
UPDATE users SET age = 30 WHERE id = 12;
Query OK, 0 rows affected (0.00 sec) |
ÐèҪעÒâµÄʱ£¬Èç¹ûÒ»¸ö×ֶεÄÀàÐÍÊÇTIMESTAMP£¬ÄÇôÕâ¸ö×Ö¶ÎÔÚÆäËü×ֶθüÐÂʱ×Ô¶¯¸üС£
ÔÚÓÐЩʱºòÎÒÃÇÐèÒªµÃµ½UPDATEËùÑ¡ÔñµÄÐÐÊý£¬¶ø²»ÊDZ»¸üеÄÐÐÊý¡£ÎÒÃÇ¿ÉÒÔͨ¹ýһЩAPIÀ´´ïµ½Õâ¸öÄ¿µÄ¡£ÈçMySQLÌṩµÄC APIÌṩÁËÒ»¸öÑ¡Ïî¿ÉÒԵõ½ÄãÏëÒªµÄ¼Ç¼Êý¡£¶øMySQLµÄJDBCÇý¶¯µÃµ½µÄĬÈϼǼÊýÒ²ÊÇÆ¥ÅäµÄ¼Ç¼Êý¡£
UPDATEºÍREPLACE»ù±¾ÀàËÆ£¬µ«ÊÇËüÃÇÖ®¼äÓÐÁ½µã²»Í¬¡£
1. UPDATEÔÚûÓÐÆ¥Åä¼Ç¼ʱʲô¶¼²»×ö£¬¶øREPLACEÔÚÓÐÖØ¸´¼Ç¼ʱ¸üУ¬ÔÚûÓÐÖØ¸´¼Ç¼ʱ²åÈë¡£
2. UPDATE¿ÉÒÔÑ¡ÔñÐԵظüмǼµÄÒ»²¿·Ö×ֶΡ£¶øREPLACEÔÚ·¢ÏÖÓÐÖØ¸´¼Ç¼ʱ¾Í½«ÕâÌõ¼Ç¼³¹µ×ɾ³ý£¬ÔÙ²åÈëеļǼ¡£Ò²¾ÍÊÇ˵£¬½«ËùÓеÄ×ֶζ¼¸üÐÂÁË¡£
Èý¡¢DELETEºÍTRUNCATE TABLE
ÔÚMySQLÖÐÓÐÁ½ÖÖ·½·¨¿ÉÒÔɾ³ýÊý¾Ý£¬Ò»ÖÖÊÇDELETEÓï¾ä£¬ÁíÒ»ÖÖÊÇTRUNCATE TABLEÓï¾ä¡£DELETEÓï¾ä¿ÉÒÔͨ¹ýWHERE¶ÔҪɾ³ýµÄ¼Ç¼½øÐÐÑ¡Ôñ¡£¶øÊ¹ÓÃTRUNCATE TABLE½«É¾³ý±íÖеÄËùÓмǼ¡£Òò´Ë£¬DELETEÓï¾ä¸üÁé»î¡£
Èç¹ûÒªÇå¿Õ±íÖеÄËùÓмǼ£¬¿ÉÒÔʹÓÃÏÂÃæµÄÁ½ÖÖ·½·¨£º
DELETE FROM table1
TRUNCATE TABLE table1 |
ÆäÖеڶþÌõ¼Ç¼ÖеÄTABLEÊÇ¿ÉÑ¡µÄ¡£
Èç¹ûҪɾ³ý±íÖеIJ¿·Ö¼Ç¼£¬Ö»ÄÜʹÓÃDELETEÓï¾ä¡£
DELETE FROM table1 WHERE ; |
Èç¹ûDELETE²»¼ÓWHERE×Ӿ䣬ÄÇôËüºÍTRUNCATE TABLEÊÇÒ»ÑùµÄ£¬µ«ËüÃÇÓÐÒ»µã²»Í¬£¬ÄǾÍÊÇDELETE¿ÉÒÔ·µ»Ø±»É¾³ýµÄ¼Ç¼Êý£¬¶øTRUNCATE TABLE·µ»ØµÄÊÇ0¡£
Èç¹ûÒ»¸ö±íÖÐÓÐ×ÔÔö×ֶΣ¬Ê¹ÓÃTRUNCATE TABLEºÍûÓÐWHERE×Ó¾äµÄDELETEɾ³ýËùÓмǼºó£¬Õâ¸ö×ÔÔö×ֶν«Æðʼֵ»Ö¸´³É1.Èç¹ûÄã²»ÏëÕâÑù×öµÄ»°£¬¿ÉÒÔÔÚDELETEÓï¾äÖмÓÉÏÓÀÕæµÄWHERE£¬ÈçWHERE 1»òWHERE true¡£
DELETE FROM table1 WHERE 1;
ÉÏÃæµÄÓï¾äÔÚÖ´ÐÐʱ½«É¨ÃèÿһÌõ¼Ç¼¡£µ«Ëü²¢²»±È½Ï£¬ÒòΪÕâ¸öWHEREÌõ¼þÓÀԶΪtrue¡£ÕâÑù×öËäÈ»¿ÉÒÔ±£³Ö×ÔÔöµÄ×î´óÖµ£¬µ«ÓÉÓÚËüÊÇɨÃèÁËËùÓеļǼ£¬Òò´Ë£¬ËüµÄÖ´Ðгɱ¾Òª±ÈûÓÐWHERE×Ó¾äµÄDELETE´óµÃ¶à¡£
DELETEºÍTRUNCATE TABLEµÄ×î´óÇø±ðÊÇDELETE¿ÉÒÔͨ¹ýWHEREÓï¾äÑ¡ÔñҪɾ³ýµÄ¼Ç¼¡£µ«Ö´ÐеÃËٶȲ»¿ì¡£¶øÇÒ»¹¿ÉÒÔ·µ»Ø±»É¾³ýµÄ¼Ç¼Êý¡£¶øTRUNCATE TABLEÎÞ·¨É¾³ýÖ¸¶¨µÄ¼Ç¼£¬¶øÇÒ²»ÄÜ·µ»Ø±»É¾³ýµÄ¼Ç¼¡£µ«ËüÖ´Ðе÷dz£¿ì¡£
Óë±ê×¼µÄSQLÓï¾ä²»Í¬£¬DELETEÖ§³ÖORDER BYºÍLIMIT×Ӿ䣬ͨ¹ýÕâÁ½¸ö×Ӿ䣬ÎÒÃÇ¿ÉÒÔ¸üºÃµØ¿ØÖÆÒªÉ¾³ýµÄ¼Ç¼¡£Èçµ±ÎÒÃÇÖ»Ïëɾ³ýWHERE×Ó¾ä¹ýÂ˳öÀ´µÄ¼Ç¼µÄÒ»²¿·Ö£¬¿ÉÒÔʹÓÃLIMIB£¬Èç¹ûҪɾ³ýºó¼¸Ìõ¼Ç¼£¬¿ÉÒÔͨ¹ýORDER BYºÍLIMITÅäºÏʹÓ᣼ÙÉèÎÒÃÇҪɾ³ýusers±íÖÐnameµÈÓÚ"Mike"µÄǰ6Ìõ¼Ç¼¡£¿ÉÒÔʹÓÃÈçϵÄDELETEÓï¾ä£º
DELETE FROM users WHERE name = 'Mike' LIMIT 6; |
Ò»°ãMySQL²¢²»È·¶¨É¾³ýµÄÕâ6Ìõ¼Ç¼ÊÇÄÄ6Ìõ£¬ÎªÁ˸ü±£ÏÕ£¬ÎÒÃÇ¿ÉÒÔʹÓÃORDER BY¶Ô¼Ç¼½øÐÐÅÅÐò¡£
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6; |