## MYSQL Àü±¹ Áö¿ª ÀüÈ­¹øÈ£ º¯°æ ################################### ## # ÀÛ¼ºÀÚ : ±èÄ¥ºÀ (´Ð:»êÀÌ) san2(at)linuxchannel.net # ÀÛ¼ºÀÏ : 2000.07.08 #################################################################### # table : wholldb # column : callfax # SQL ¹®Àº Àû¼ö³× µ¿³×ÀÇ 1¿øÂ¥¸® ÆÁ¶õ¿¡¼­... # MySQL ¹öÀü : 3.22.32 # # »ç¿ë ¿¹ : $ mysql -u DBUSER -p DBNAME < THIS_SQL.sql # # ****** ÁÖÀÇ ¾Æ·¡ÀÇ SQL ¼ø¼­¸¦ ¹Ù²ÙÁö ¸¶½Ê½Ã¿À. ****************** # ## ³»¿ë Ãâó : manual_Reference.html#String_functions ## #################################################################### ## CONCAT() ÇÔ¼ö : ## MySQLÀÇ ¹®ÀÚ¿­ ÇÔ¼ö Áß ÇϳªÀ̸ç, ¹®ÀÚ¿­ ÀÎÀÚÀÎ str1,str2,...¸¦ ¿¬°áÇÏ´Â ±â´ÉÀ» ÇÑ´Ù. ## ±âº»Çü½ÄÀº CONCAT(str1,str2[,str3,...])À̸ç ÃÖ¼ÒÇÑ 2°³ÀÇ ÀÎÀÚ°¡ ÇÊ¿äÇÏ´Ù.(¿¬°áÇØ¾ß ÇϹǷÎ) ## ## CONCAT(str1,str2,...) ## Returns the string that results from concatenating the arguments. ## Returns NULL if any argument is NULL. May have more than 2 arguments. ## A numeric argument is converted to the equivalent string form. ## ## mysql> select CONCAT('My', 'S', 'QL'); ## -> 'MySQL' ## mysql> select CONCAT('My', NULL, 'QL'); ## -> NULL ## mysql> select CONCAT(14.3); ## -> '14.3' ## #################################################################### ## SUBSTRING_INDEX() ÇÔ¼ö : ## ¹®ÀÚ¿­ ÇÔ¼ö Áß ÇϳªÀ̸ç, ±¸¼º ÀÎÀÚÁß strÀ» ƯÁ¤ ¹®ÀÚ(¿¹: -)·Î ±¸ºÐ(delim)ÇÏ¿© ¿ø¼Ò¸¦ ¸¸µç ÈÄ, ## ƯÁ¤(count) ¿ø¼Ò¸¦ Ãâ·ÂÇÑ´Ù. ## ±âº» Çü½ÄÀº SUBSTRING_INDEX(str,delim,count)À̸ç count°¡ ¾ç¼öÀÌ¸é ¿ÞÂÊ ¿ø¼Ò¿¡¼­, À½¼öÀÌ¸é ¿À¸¥ÂÊ ¿ø¼Ò¿¡¼­ ## count ¸¸Å­ Ãâ·ÂÇÑ´Ù. ## ## SUBSTRING_INDEX(str,delim,count) ## Returns the substring from string str before count occurrences of the delimiter delim. ## If count is positive, everything to the left of the final delimiter (counting from the left) is returned. ## If count is negative, everything to the right of the final delimiter (counting from the right) is returned. ## ## mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2); ## -> 'www.mysql' ## mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2); ## -> 'mysql.com' ## ## This function is multi-byte safe. ## ## ÀÌ¿Í ºñ½ÁÇÑ ÇÔ¼ö·Î, ## SUBSTRING(str,pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos), SUBSTRING(str FROM pos FOR len) ## µîÀÌ ÀÖ´Ù. ## ## REGEXP´Â Á¤±Ô Ç¥Çö½ÄÀ» »ç¿ëÇÒ °æ¿ì # #################################################################### # UPDATE wholldb SET callfax = CONCAT("033-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^0353-"; # °­¿øµµ ö¿ø UPDATE wholldb SET callfax = CONCAT("033-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^03[679][0-9]-"; # °­¿øµµ ÀüºÎ UPDATE wholldb SET callfax = CONCAT("031-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^03[3-5][0-9]-"; # °æ±âµµ ÀüºÎ UPDATE wholldb SET callfax = CONCAT("055-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^05[259][0-9]-"; # °æ³² ÀüºÎ UPDATE wholldb SET callfax = CONCAT("054-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^05[4678][0-9]-"; # °æºÏ ÀüºÎ UPDATE wholldb SET callfax = CONCAT("063-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^068[13]-"; # ÀüºÏ Á¤À¾/ºÎ¾È UPDATE wholldb SET callfax = CONCAT("061-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^06[13689][0-9]-"; # Àü³² ÀüºÎ UPDATE wholldb SET callfax = CONCAT("063-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^06[57][0-9]-"; # ÀüºÏ ÀüºÎ UPDATE wholldb SET callfax = CONCAT("043-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^0414-"; # ÃæºÏ ¿µµ¿ UPDATE wholldb SET callfax = CONCAT("041-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^04[156][0-9]-"; # Ãæ³² ÀüºÎ UPDATE wholldb SET callfax = CONCAT("043-", SUBSTRING_INDEX(callfax,'-',-2)) WHERE callfax REGEXP "^04[347][0-9]-"; # ÃæºÏ ÀüºÎ # # #################################################################### # ¾Æ·¡ÀÇ ¿¹´Â PHPȯ°æ¿¡¼­ ÀÛ¾÷ÇÏ´Â ¿¹ÀÔ´Ï´Ù....Àû¼ö³× µ¿³×¿¡ º»ÀÎÀÌ Æ÷½ºÆÃÇÑ ³»¿ëÀÔ´Ï´Ù. #################################################################### # # $callfax ¶ó´Â º¯¼ö´Â for()¹®À̳ª while()À¸·Î ¸¸µé¾îÁö¹Ç·Î # Àú °°Àº °æ¿ì¿¡´Â for()À» »ç¿ëÇß½À´Ï´Ù. # # ... # # $result = mysql_query("SELECT count(no) FROM $table"); # $total_record = mysql_result($result,0,0); // Àüü ·¹ÄÚµå ¼ö¸¦ ±¸ÇÑ´Ù. # ... # # $result = mysql_query("SELECT no,callfax,addr FROM $table ORDER BY no DESC"); # // Âü°í·Î no Ä÷³Àº ÀÚ¿¬Áõ°¡ÇÑ Àý´ë¹øÈ£ÀÔ´Ï´Ù... # // callfax´Â ÀüÈ­-Æѽº¹øÈ£ Ä÷³ÀÔ´Ï´Ù... # # ... # # for($i = 0; $i <= ($total_record-1); $i++) { # $no = mysql_result($result,$i,0); # $callfax = mysql_result($result,$i,1); # $addr = mysql_result($result,$i,2); # # $callfax = eregi_replace("^0353-", "033-", $callfax); // °­¿øµµ ö¿ø # $callfax = eregi_replace("^03[679][0-9]-", "033-", $callfax); // °­¿øµµ ÀüºÎ # $callfax = eregi_replace("^03[3-5][0-9]-", "031-", $callfax); // °æ±âµµ ÀüºÎ # $callfax = eregi_replace("^05[259][0-9]-", "055-", $callfax); // °æ³² ÀüºÎ # $callfax = eregi_replace("^05[4678][0-9]-", "054-", $callfax); // °æºÏ ÀüºÎ # $callfax = eregi_replace("^068[13]-", "063-", $callfax); // ÀüºÏ Á¤À¾/ºÎ¾È # $callfax = eregi_replace("^06[13689][0-9]-", "061-", $callfax); // Àü³² ÀüºÎ # $callfax = eregi_replace("^06[57][0-9]-", "063-", $callfax); // ÀüºÏ ÀüºÎ # $callfax = eregi_replace("^0414-", "043-", $callfax); // ÃæºÏ ¿µµ¿ # $callfax = eregi_replace("^04[156][0-9]-", "041-", $callfax); // Ãæ³² ÀüºÎ # $callfax = eregi_replace("^04[347][0-9]-", "043-", $callfax); // ÃæºÏ ÀüºÎ # # mysql_query("UPDATE $table SET callfax='$callfax' WHERE no ='$no'"); # // ÇÊÅ͸µ(?)ÇÑ ÀüÈ­¹øÈ£¸¦ ´Ù½Ã DB¿¡ ³Ö´Â´Ù. # # echo ("no:$no $callfax($addr)
\n"); # } # # ... # # ÀÌ»óÀÔ´Ï´Ù... # # Âü°í·Î... # PHP4pl2¿¡¼­ 2000¿©°³ÀÇ DATA¸¦ °íÄ¡´Âµ¥ ¾à 1ÃÊ Á¤µµ °É¸®´Â ±º¿ä..(AMD K6-3 400Mhz) # ¸¸¾à PHP3·Î ÀÛ¾÷ÇϽô ºÐÁß¿¡¼­ ÀÌ º¸´Ù ´õ ¸¹Àº ¾çÀ» °ü¸®ÇϽô ºÐÀº # ¹Ýµå½Ã php3.ini(¶Ç´Â php.ini)¿¡¼­ # # max_execution_time = 30 # # ºÎºÐÀ» ³Ë³ËÇÏ°Ô Á¤ÇÏ°í ³ª¼­ À¥¼­¹ö¸¦ ¹Ýµå½Ã Àç°¡µ¿ÇÏ½Ã±æ ¹Ù¶ø´Ï´Ù. # (PHP¸¦ ´Ù½Ã ÄÄÆÄÀÏÇØ¾ß ÇÒ µí...) # ¾ÕÀÇ °æ¿ì´Â ÃÖ´ë ½ºÅ©¸³Æ® ½ÇÇà½Ã°£ÀÌ 30ÃÊ ¶ó´Â ÀǹÌÀÔ´Ï´Ù. # (1000¸¸±îÁö for()¹®À» µ¹·ÈºÃ´õ´Ï¸¸ "¸øÇÏ°Ú´Ù°í" ¿¡·¯¸¦ ³»´õ±º¿ä...) # # *PS : # Ȥ½Ã ³ëÆĽɿ¡¼­.. # ¹Ýµå½Ã ¹é¾÷ÇϽðí ÀÛ¾÷À» ÁøÇàÇÏ½Ã±æ ¹Ù¶ø´Ï´Ù... # ÀÛ¾÷ÀÌ ³¡³ª°í À§ÀÇ ÇÊÅ͸µ(?)ÇÑ ºÎºÐ°ú UPDATE SQL ºÎºÐÀº Áö¿öÁÖ½Ã±æ ¹Ù¶ø´Ï´Ù. # # À§ÀÇ ³»¿ëÀº Ãʺ¸ÀÚ¸¦ À§ÇÑ ÇϳªÀÇ ¹æ¹ýÀ» Á¦½ÃÇÑ °Í »ÓÀÔ´Ï´Ù. #