----------------------------------------- INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] table_name ... REPLACE [LOW_PRIORITY | DELAYED] [INTO] table_name ... UPDATE [LOW_PRIORITY] [IGNORE] table_name ... DELETE [LOW_PRIORITY] [QUICK] FROM table_name ... ALTER [IGNORE] TABLE table_name ... SELECT [HIGH_PRIORITY] ... FROM table_name ... ----------------------------------------- LOW_PRIORITY : µ¥ÀÌÅ͸¦ SELECT °¡ ¾Æ´Ñ Ãß°¡, ¼öÁ¤, »èÁ¦ÇÒ °æ¿ì¿¡ ÇØ´çµÇ¸ç, ´Ù¸¥ Ŭ¶óÀ̾ðÆ®°¡ SELECT ¸¦ ¸ðµÎ ¸¶Ä¥¶§±îÁö ±â´Ù¸° ÈÄ ½ÇÇà DELAYED : µ¥ÀÌÅ͸¦ Ãß°¡ÇÒ °æ¿ì¿¡ ÇØ´çµÇ¸ç, ¸Þ¸ð¸®¿¡ ÀúÀåÇØ ³õ°í ó¸®ÇÔ (Ŭ¶óÀ̾ðÆ®´Â SELECT °¡ ³¡³¯¶§±îÁö ±â´Ù¸®Áö ¾ÊÀ½) Ç×»ó '1 rows affected' ÀÇ °á°ú¸¦ ³¿ IGNORE : PRIMARY KEY, UNIQUE KEY ¿¡ ´ëÇÑ µ¿ÀÏÇÑ µ¥ÀÌÅ͸¦ Ãß°¡, ¼öÁ¤, »èÁ¦ÇÒ °æ¿ì¿¡ ÇØ´çµÇ¸ç, ÀÌ·± ¿¡·¯¸¦ ¹«½ÃÇÏ°í °è¼Ó ÁøÇàÇÔ. HIGH_PRIORITY : µ¥ÀÌÅ͸¦ SELECT °Ë»öÇÒ °æ¿ì¿¡¸¸ ÇØ´çµÇ¸ç, ´Ù¸¥ ÀÛ¾÷º¸´Ù ¿ì¼±¼øÀ§°¡ ³ôÀ½(SELECT °¡ ´õ ºó¹øÇÑ °æ¿ì¿¡ »ç¿ë) ----------------------------------------- ¿¹Á¦1) ÀÔ·Â > °Ë»ö µ¥ÀÌÅÍÀÇ Ãß°¡, ¼öÁ¤, »èÁ¦°¡ °Ë»ö(SELECT) º¸´Ù ´õ ºó¹øÇÏ¿© ÀÔ·ÂÂÊ¿¡ ´õ ºñÁßÀ» µÎ°íÀÚ ÇÑ´Ù¸é, µ¥ÀÌÅÍÀÇ ºü¸¥ Ãß°¡, ¼öÁ¤, »èÁ¦°¡ ¿ä±¸µÇ¹Ç·Î, INSERT DELAYED [IGNORE] [INTO] table_name ... REPLACE DELAYED [INTO] table_name ... UPDATE [IGNORE] table_name ... DELETE QUICK FROM table_name ... SELECT ... FROM table_name ... ÀÌ¿Í SELECT ¿¡´Â HIGH_PRIORITY ¸¦ »ç¿ëÇÏÁö ¾Ê´Â °ÍÀÌ ÁÁÀ¸¸ç, INSERT ³ª REPLACE ¿¡´Â DELAYED ¸¦ »ç¿ëÀ» ±ÇÇÔ. ¿¹Á¦2) ÀÔ·Â < °Ë»ö µ¥ÀÌÅÍÀÇ °Ë»ö(SELECT)ÀÌ Ãß°¡, ¼öÁ¤, »èÁ¦ º¸´Ù ´õ ºó¹øÇÏ¿© µ¥ÀÌÅÍÀÇ °Ë»öÂÊ¿¡ ´õ ºñÁßÀ» µÎ°íÀÚ ÇÑ´Ù¸é, ´õ ºü¸¥ °Ë»öÀÌ ¿ä±¸µÇ¹Ç·Î ¿¹Á¦1°ú ±× ¹Ý´ë·Î, INSERT LOW_PRIORITY [IGNORE] [INTO] table_name ... REPLACE LOW_PRIORITY [INTO] table_name ... UPDATE LOW_PRIORITY [IGNORE] table_name ... DELETE LOW_PRIORITY FROM table_name SELECT HIGH_PRIORITY ... FROM table_name ... ÀÌ¿Í SELECT ¿¡´Â HIGH_PRIORITY ¸¦ »ç¿ëÇÏ´Â °ÍÀÌ ÁÁÀ¸¸ç, ³ª¸ÓÁö´Â LOW_PRIORITY »ç¿ëÀ» ±ÇÇÔ. *Âü°í) CREATE TABLE table_name ( ... ) PACK_KEYS=1; or ALTER TABLE table_name PACK_KEYS=1; ¿¹Á¦3) ÀÔ·Â '=. °Ë»ö µ¥ÀÌÅÍ ÀԷ°ú °Ë»öÀ» ºñ½ÁÇÑ ºñÁßÀ¸·Î ÇÏ°íÀÚ ÇÑ´Ù¸é, INSERT DELAYED [IGNORE] REPLACE DELAYED UPDATE LOW_PRIORITY [IGNORE] <-- ¾Æ¹«·¡µµ SELECT º¸´Ù ´õ ºñÁßÀÌ ÀûÀ¸¹Ç·Î DELETE LOW_PRIORITY QUICK <-- ¾Æ¹«·¡µµ SELECT º¸´Ù ´õ ºñÁßÀÌ ÀûÀ¸¹Ç·Î SELECT HIGH_PRIORITY ÀÌ¿Í °°ÀÌ SELECT ÂÊ¿¡ ¾à°£ ´õ ºñÁßÀ» µÎ¾î SQL ¹®À» ¿Ï¼ºÇÏ´Â °ÍÀÌ ÁÁÀ½. *Âü°í1) INSERT INTO ¿Í REPLACE INTO ·¹Äڵ忡 ¾î¶² µ¥ÀÌÅ͸¦ Ãß°¡ÇÒ¶§ ±âÁ¸¿¡ °°Àº PRIMARY KEY °¡ ÀÖÀ¸¸é INSERT ´Â ¿¡·¯¸¦ ³¿. ¹Ý¸é, REPLACE ´Â ±âÁ¸¿¡ °°Àº PRIMARY KEY °¡ ÀÖÀ¸¸é, ±âÁ¸ÀÇ ·¹Äڵ带 »èÁ¦(DELETE)ÇÏ°í »õ·Î INSERT Çϱ⠶§¹®¿¡ Ç×»ó '2 rows affected' ÀÇ °á°ú°¡ µÇ°í(DELETE and INSERT), °°Àº PRIMARY KEY °¡ ¾øÀ¸¸é À̶§´Â INSERT ¿Í µ¿ÀÏÇÏ°Ô '1 rows affected' °¡ µÊ. ¸¸¾à Å×ÀÌºí¿¡¼ PRIMARY KEY ³ª UNIQUE ÇÑ KEY °¡ ¾ø´Ù¸é, REPLACE ±¸¹®Àº ÇÊ¿ä°¡ ¾øÀ¸¸ç, À̶§´Â INSERT ±¸¹®À» »ç¿ëÇØ¾ß ÇÔ *Âü°í2) INSERT INTO ¿Í INSERT DELAYED INTO INSERT INTO ´Â ±âÁ¸¿¡ °°Àº PRIMARY KEY °¡ ÀÖÀ¸¸é ¿¡·¯¸¦ ³»Áö¸¸, INSERT DELAYED INTO ³»ºÎÀûÀ¸·Î SELECT ¿Í INSERT °¡ ÀÌ·ç¾îÁö±â ¶§¹®¿¡ ¿¡·¯¾øÀÌ Ç×»ó '1 rows affected' °¡ ÀÌ·ç¾îÁü. Áï, INSERT DELAYED INTO ´Â ±âÁ¸¿¡ °°Àº PRIMARY KEY °¡ ÀÖÀ¸¸é, ÇöÀçÀÇ µ¥ÀÌÅ͸¦ ´õ ÀÌ»ó INSERT ¸¦ ÇÏÁö ¾Ê°í, Ç×»ó '1 rows affected' ÀÇ °á°ú¸¦ º¸³¿. ¹Ù²Ù¾î ¸»Çϸé, ±âÁ¸ÀÇ ·¹ÄÚµå´Â ¼öÁ¤(DELETE, UPDATE, REPLACE)µÇÁö ¾Ê°í ±×´ë·Î Àֱ⠶§¹®¿¡, PRIMARY KEY ¿Ü¿¡ ´Ù¸¥ µ¥ÀÌÅÍ°¡ ¼·Î ´Ù¸¦Áö¶óµµ °»½ÅµÇÁö ¾ÊÀ½ ¹Ù·Î ÀÌÁ¡ÀÌ REPLACE INTO ³ª REPLACE DELAYED INTO ¿Í ¼·Î ´Ù¸§. (REPLACE ´Â ±âÁ¸ÀÇ ·¹Äڵ带 »èÁ¦ÇÏ°í »õ·Î¿î µ¥ÀÌÅÍ°¡ Ãß°¡µÇ±â ¶§¹®¿¡ PRIMARY KEY ¿ÜÀÇ ´Ù¸¥ µ¥ÀÌÅÍ´Â °»½ÅµÊ). MySQL ¸Å´º¾ó¿¡ ÀÇÇϸé, INSERT DELAYED INTO ±¸¹®Àº ISAM, MyISAM Å×ÀÌºí ¿¡¼¸¸ °¡´ÉÇÏ°í, »ç¿ëÇÒ ¼ö ¾ø´Â Å×ÀÌºí¿¡¼´Â INSERT º¸´Ù ´À·ÁÁø´Ù°í ÇÔ (HEAP Å×ÀÌºíµµ °¡´ÉÇÔ) Àû¿ë¿¹ 1. SELECT COONT(*) FROM table WHERE mixed_primary_key_compare 2. ¸¸¾à COONT(*) °ªÀÌ ¾ø´Ù¸é, INSERT INTO table ... ¸¸¾à COUNT(*) °ªÀÌ ÀÖ´Ù¸é, SKIP. ÀÌ 2°¡Áö °úÁ¤À» Çѹø¿¡ ó¸®ÇÒ ¼ö ÀÖ´Â °ÍÀÌ ¹Ù·Î INSERT DELAYED INTO ÀÓ INSERT DELAYED INTO table ... ¸¸¾à PRIMARY KEY ¿ÜÀÇ ´Ù¸¥ °ªµéÀÌ Ç×»ó »õ·Î¿î °ªÀ¸·Î °»½ÅµÇ±â ¿øÇÑ´Ù¸é REPLACE INTO ³ª REPLACE DELAYED INTO ¸¦ »ç¿ëÇÏ¸é µÊ. *Âü°í3) REPLACE INTO ¿Í REPLACE DELAYED INTO ¿ì¼± ÀÌ µÎ°³ÀÇ °á°ú´Â Ç×»ó µ¿ÀÏÇϸç, ±âÁ¸¿¡ °°Àº PRIMARY KEY °¡ ÀÖÀ¸¸é REPLACE INTO ´Â ±âÁ¸ÀÇ ·¹Äڵ带 »èÁ¦ÇÏ°í INSERT ÇÏ´Â ¹Ý¸é, REPLACE DELAYED INTO ´Â ±âÁ¸ÀÇ ·¹Äڵ带 UPDATE ÇÏ´Â ¹æ½ÄÀ̹ǷΠÇ×»ó '1 rows affected' ÀÇ °á°ú¸¦ º¸³¿. Àû¿ë¿¹ 1. SELECT COONT(*) FROM table WHERE mixed_primary_key_compare 2. ¸¸¾à COUNT(*) °ªÀÌ ¾ø´Ù¸é, INSERT INTO table ... ¸¸¾à COONT(*) °ªÀÌ ÀÖ´Ù¸é, UPDATE TABLE table SET ... WHERE mixed_primary_key_compare ÀÌ 2°¡Áö °úÁ¤À» ÇѲ¨¹ø¿¡ ó¸®ÇÒ ¼ö ÀÖ´Â ¹æ¹ýÀÌ ¹Ù·Î, REPLACE DELAYED INTO table ... ±âÁ¸¿¡ °°Àº PRIMARY KEY °¡ ¾ø´Ù¸é °á°úÀûÀ¸·Î INSERT ÇÔ. *Âü°í4) INSERT DELAYED INTO ¿Í REPLACE DELAYED INTO DELAYED ±¸¹®Àº Ç×»ó '1 rows affected' ÀÇ °á°ú¸¦ º¸³½´Ù´Â Á¡À» °¨¾ÈÇϸé, ÀüÀÚ´Â SELECT + INSERT ÀÌ°í, ÈÄÀÚ´Â SELECT + UPDATE ÀÇ Çü½ÄÀÓ. Áï, ±âÁ¸¿¡ °°Àº PRIMARY KEY °¡ ÀÖ´Ù¸é, ÀüÀÚ´Â Ç×»ó ±âÁ¸ÀÇ ·¹ÄÚµå´Â ±×´ë·Î ÀÖ°í(´Ù¸¥ Ä÷³µµ °»½ÅÇÏÁö ¾ÊÀ½), ÈÄÀÚ´Â Ç×»ó »õ·Î¿î ·¹ÄÚµå·Î UPDATE ÇÔ. ±âÁ¸¿¡ °°Àº PRIMARY KEY °¡ ¾ø´Ù¸é °á°úÀûÀ¸·Î ¸ðµÎ INSERT ÇÔ. Á¤¸®ÇÏ¸é ´ÙÀ½°ú °°À½. <FONT FACE='±¼¸²Ã¼'> +-----------------+----------------------+-------------+----------+----------+ | ±¸ºÐ(same KEY) | exits | not exists | ±âÁ¸ row | ºñ°í | |-----------------+----------------------+-------------+----------+----------| | INSERT | error(FALSE), (0) | INSERT, (1) | | | |-----------------+----------------------+-------------| °»½Å(X) |----------| | INSERT DELAYED | SKIP(TRUE), (1) | INSERT, (1) | | Ç×»ó (1) | |-----------------+----------------------+-------------+----------+----------| | REPLACE | DELETE + INSERT, (2) | INSERT, (1) | | | |-----------------+----------------------+-------------| °»½Å(O) |----------| | REPLACE DELAYED | UPDATE, (1) | INSERT, (1) | | Ç×»ó (1) | +-----------------+----------------------+-------------+----------+----------+ </FONT> *ÁÖ1) 'same KEY' ´Â PRIMARY KEY ¶Ç´Â UNIQUE KEY ¿¡ ´ëÇÑ row Á¸Àç ¿©ºÎ *ÁÖ2) PRIMARY KEY ¶Ç´Â UNIQUE KEY ¼³Á¤ÀÌ ¾øÀ¸¸é REPLACE ´Â ¹«ÀǹÌÇÔ. *ÁÖ3) °ýÈ£() ¾ÈÀÇ ¼ýÀÚ´Â 'number of rows affected'¸¦ ÀǹÌÇÔ.
from http://linuxchannel.net/board/read.php?table=alpha&no=68