MySQL replace into Ó÷¨
ÔÚÏò±íÖвåÈëÊý¾ÝµÄʱºò£¬¾³£Óöµ½ÕâÑùµÄÇé¿ö£º1. Ê×ÏÈÅжÏÊý¾ÝÊÇ·ñ´æÔÚ£» 2. Èç¹û²»´æÔÚ£¬Ôò²åÈ룻3.Èç¹û´æÔÚ£¬Ôò¸üС£
ÔÚ SQL Server ÖпÉÒÔÕâÑù´¦Àí£º
if not exists (select 1 from t where id = 1) insert into t(id, update_time) values(1, getdate()) else update t set update_time = getdate() where id = 1
ÄÇô MySQL ÖÐÈçºÎʵÏÖÕâÑùµÄÂß¼ÄØ£¿±ð׿±£¡MySQL ÖÐÓиü¼òµ¥µÄ·½·¨£º replace into
replace into t(id, update_time) values(1, now());
»ò
replace into t(id, update_time) select 1, now();
replace into ¸ú insert ¹¦ÄÜÀàËÆ£¬²»Í¬µãÔÚÓÚ£ºreplace into Ê×Ïȳ¢ÊÔ²åÈëÊý¾Ýµ½±íÖУ¬ 1. Èç¹û·¢ÏÖ±íÖÐÒѾÓдËÐÐÊý¾Ý£¨¸ù¾ÝÖ÷¼ü»òÕßΨһË÷ÒýÅжϣ©ÔòÏÈɾ³ý´ËÐÐÊý¾Ý£¬È»ºó²åÈëеÄÊý¾Ý¡£ 2. ·ñÔò£¬Ö±½Ó²åÈëÐÂÊý¾Ý¡£
ҪעÒâµÄÊÇ£º²åÈëÊý¾ÝµÄ±í±ØÐëÓÐÖ÷¼ü»òÕßÊÇΨһË÷Òý£¡·ñÔòµÄ»°£¬replace into »áÖ±½Ó²åÈëÊý¾Ý£¬Õ⽫µ¼Ö±íÖгöÏÖÖظ´µÄÊý¾Ý¡£
MySQL replace into ÓÐÈýÖÖÐÎʽ£º
1. replace into tbl_name(col_name, ...) values(...) 2. replace into tbl_name(col_name, ...) select ... 3. replace into tbl_name set col_name=value, ...
Ç°Á½ÖÖÐÎʽÓõĶàЩ¡£ÆäÖÐ “into” ¹Ø¼ü×Ö¿ÉÒÔÊ¡ÂÔ£¬²»¹ý×îºÃ¼ÓÉÏ “into”£¬ÕâÑùÒâ˼¸ü¼ÓÖ±¹Û¡£ÁíÍ⣬¶ÔÓÚÄÇЩûÓиøÓèÖµµÄÁУ¬MySQL ½«×Ô¶¯ÎªÕâЩÁи³ÉÏĬÈÏÖµ¡£
Ïà¹ØÎĵµ£º
½ñÌìÓÃJPAÉú³ÉÊý¾Ý±í£¬ËäÈ»ÔÚEntityÉÏÒѾÅäÖúÃÁËÒ»¶Ô¶àºÍ¶à¶ÔÒ»µÄ¹Øϵ£¬×ÜÊÇÆæ¹Ö²»ÄÜÉú³ÉÍâ¼üË÷Òý¡£
ºóÀ´·¢ÏÖÊÇMySQLµÄ storage engine µÄÎÊÌâ¡£
ĬÈϵÄMyISAMÊDz»Ö§³ÖÍâ¼üµÄ¡£
Ö»ÓÐ¸Ä³É InnoDB²ÅÖ§³ÖÍâ¼ü
Ð޸ķ½·¨£¬ÕÒµ½ my.ini
ÔÚÀïÃæÌí¼Ó»òÐ޸ģ¨Èç¹ûÓеĻ°£©
default-storage-engine = InnoDB
¸½£º
MyISAMºÍ ......
Ò»Ö±ÒÔΪmysqlËæ»ú²éѯ¼¸ÌõÊý¾Ý£¬¾ÍÓÃ
SELECT
*
from
`
table
`
ORDER
BY
RAND
() LIMIT
5
¾Í¿ÉÒÔÁË¡£
µ«ÊÇÕæÕý²âÊÔһϲŷ¢ÏÖÕâÑùЧÂʷdz£µÍ¡£Ò»¸ö15ÍòÓàÌõµÄ¿â£¬²éѯ5ÌõÊý¾Ý£¬¾ÓȻҪ8ÃëÒÔÉÏ
²é¿´¹Ù·½ÊֲᣬҲ˵rand()·ÅÔÚORDER BY ×Ó¾äÖлᱻִÐж ......
ÒÔϵÄÎÄÕÂÊÇMySQL grantÓï·¨µÄÏêϸ½âÎö£¬Èç¹ûÄã¶ÔMySQL grantÓï·¨µÄÏà¹ØµÄʵ¼Ê²Ù×÷ÓÐÐËȤµÄ»°£¬Äã¾Í¿ÉÒÔ¶ÔÒÔϵÄÎÄÕµã»÷¹Û¿´ÁË¡£ÎÒÃÇ´ó¼Ò¶¼ÖªµÀMySQLÊý¾Ý¿â¸³ÓèÓû§È¨ÏÞÃüÁîµÄ¼òµ¥¸ñʽ¿É¸ÅÀ¨Îª£º
grant ȨÏÞ on Êý¾Ý¿â¶ÔÏó to Óû§
Ò»¡¢grant ÆÕͨÊý¾ÝÓû§£¬²éѯ¡¢²åÈë¡¢¸üС¢É¾³ý Êý¾Ý¿âÖÐËùÓбíÊý¾ÝµÄȨÀû¡£
grant ......
¸ømysql¿ªÒ»¸ö¿ÉÒÔÔ¶³Ì·ÃÎʵÄÓû§ÃüÁîÈçÏÂ:
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
"*.*" ±íʾÊý¾Ý¿âÖÐËùÓеıí,Ò²¿ÉÒÔ»»³ÉÖ¸¶¨µÄ±íÈçtest.*
"myuser" ±íʾµÄÊÇ·ÃÎʵÄÓû§Ãû;
"%" ±íʾËùÓеÄÖ÷»ú¼´ÈκεÄÖ÷»ú¶¼¿ÉÒÔͨ¹ýÕâ¸öÓû§Ãû·ÃÎÊÎÒµÄÊý¾Ý¿â,Ò²¿ÉÒԸóÉÌض¨µ ......