MySQLÖÐselect * for updateËø±íµÄÎÊÌâ
select * for updateËø±íµÄÎÊÌâ
ÓÉÓÚInnoDBÔ¤ÉèÊÇRow-Level Lock£¬ËùÒÔÖ»ÓС¸Ã÷È·¡¹µÄÖ¸¶¨Ö÷¼ü£¬MySQL²Å»áÖ´ÐÐRow lock (Ö»Ëø×¡±»Ñ¡È¡µÄ×ÊÁÏÀý) £¬·ñÔòMySQL½«»áÖ´ÐÐTable Lock (½«Õû¸ö×ÊÁÏ±íµ¥¸øËø×¡)¡£
¾Ù¸öÀý×Ó:
¼ÙÉèÓиö±íµ¥products £¬ÀïÃæÓÐid¸úname¶þ¸öÀ¸Î»£¬idÊÇÖ÷¼ü¡£
Àý1: (Ã÷È·Ö¸¶¨Ö÷¼ü£¬²¢ÇÒÓд˱Ê×ÊÁÏ£¬row lock)
SELECT * from products WHERE id='3' FOR UPDATE;
Àý2: (Ã÷È·Ö¸¶¨Ö÷¼ü£¬Èô²éÎ޴˱Ê×ÊÁÏ£¬ÎÞlock)
SELECT * from products WHERE id='-1' FOR UPDATE;
Àý2: (ÎÞÖ÷¼ü£¬table lock)
SELECT * from products WHERE name='Mouse' FOR UPDATE;
Àý3: (Ö÷¼ü²»Ã÷È·£¬table lock)
SELECT * from products WHERE id<>'3' FOR UPDATE;
Àý4: (Ö÷¼ü²»Ã÷È·£¬table lock)
SELECT * from products WHERE id LIKE '3' FOR UPDATE;
×¢1: FOR UPDATE½öÊÊÓÃÓÚInnoDB£¬ÇÒ±ØÐëÔÚ½»Ò×Çø¿é(BEGIN/COMMIT)ÖвÅÄÜÉúЧ¡£
×¢2: Òª²âÊÔ
Ëø¶¨µÄ×´¿ö£¬¿ÉÒÔÀûÓÃMySQLµÄCommand Mode £¬¿ª¶þ¸öÊÓ´°À´×ö²âÊÔ¡£
Ïà¹ØÎĵµ£º
1.INµÄÓ÷¨
IN(value,value1,...) valuesµÄ¸öÊý¸úmax_allowed_packetÏà¹Ø£¬SELECT 2 IN (2,4,'STRING');
NOT IN(value,..)ºÍINÓï·¨Ò»Ñù¡£
2.IF NOT EXISTS
CREATE ... IF NOT EXISTS stm; ...¿ÉÒÔʹDATABASE,TABLE,EVENT¡£ÈçCREATE TABLE IF NOT EXISTS t(id INT);
3.MD5,MySQLÊý¾Ý¿âÌṩÁËmd5¼ÓÃܹ¦Ä ......
Èç¹ûÄãËÑË÷GOOGLE£¬Äã»áÕÒµ½ºÜ¶à¹ØÓÚmysqlµÄËæ»úÊý£¬ËûÃÇ»áÕâÑù˵
Éú³É£³£¬£¶Ö®¼äµÄËæ»úÕûÊý
FLOOR(3 + (RAND() * 6))
ÔÚ4ºóÃæÌí¼ÓÒ»¸ö3µ½6Ö®¼äµÄÕûÊý
CONCAT(343,FLOOR(3 + (RAND() * 6)))
ÔÚijһ×ֶκóÃæÌí¼Óһλ£³£¬£¶Ö®¼äµÄÕûÊý
UPDATE test SET xx= CONCAT(xx,FLOOR(3 +(RAND() * 6)));
µ«ÊÇÎÒ˽×Ô²âÊÔÁËһϣ¬ ......
Query ProfilerÊÇMYSQL×Ô´øµÄÒ»ÖÖqueryÕï¶Ï·ÖÎö¹¤¾ß£¬Í¨¹ýËü¿ÉÒÔ·ÖÎö³öÒ»ÌõSQLÓï¾äµÄÐÔÄÜÆ¿¾±ÔÚʲôµØ·½¡£Í¨³£ÎÒÃÇÊÇʹÓõÄexplain,ÒÔ¼°slow query log¶¼ÎÞ·¨×öµ½¾«È··ÖÎö£¬µ«ÊÇQuery ProfilerÈ´¿ÉÒÔ¶¨Î»³öÒ»ÌõSQLÓï¾äÖ´Ðеĸ÷ÖÖ×ÊÔ´ÏûºÄÇé¿ö£¬±ÈÈçCPU£¬IOµÈ£¬ÒÔ¼°¸ÃSQLÖ´ÐÐËùºÄ·ÑµÄʱ¼äµÈ¡£²»¹ý¸Ã¹¤¾ßÖ»ÓÐÔÚMYSQL 5.0.37ÒÔ ......
À´×Ô£ºhttp://it.china-b.com/zx/linuxsjk/20090525/24129_1.html
ÕªÒª£ºMySQLÊÇÒ»¸ö¸ßËÙ¶È¡¢¸ßÐÔÄÜ¡¢¶àÏ̵߳ĹØÏµÐÍÊý¾Ý¿â
ÖÎÀíϵͳ£¬ÊÊÓÃÆ½Ì¨¶à£¬¿ÉÀ©Õ¹ÐÔÇ¿¡£ÔÚʵ¼ÊÉú²ú»·¾³ÖУ¬²¿ÊðºÍʵÏÖ¾ßÓÐÒ»¶¨¸ºÔؾùºâ¹¦ÄܵÄMySQL·þÎñÆ÷¼¯Èº£¬¶ÔÓÚÌá¸ßÓû§Êý¾Ý¿â
Ó¦ÓÃϵͳµÄÐÔÄÜ¡¢ËٶȺÍÎȶ¨ÐÔ¾ßÓÐÃ÷ÏÔµÄ×÷Óᣱ¾ÎļòÒª½éÉÜÁËÔÚ ......