MySQLµÄÍâ¼üºÍ²ÎÕÕÍêÕûÐÔ
Ò»¡¢²ÎÕÕÍêÕûÐÔ(Referential integrity)ºÍÍâ¼ü(foreign key)
²ÎÕÕÍêÕûÐÔ(Referential integrity)ÊÇÓÃÓÚÔ¼¶¨Á½¸ö¹Øϵ֮¼äµÄÁªÏµ£¬ÀíÂÛÉϹ涨£ºÈôMÊǹØϵSÖеÄÒ»ÊôÐÔ×飬ÇÒMÊÇÁíÒ»¹ØϵZµÄÖ÷¹Ø¼ü×Ö£¬Ôò³ÆMΪ¹ØϵS¶ÔÓ¦¹ØϵZµÄÍâ¹Ø¼ü×Ö¡£ÈôMÊǹØϵSµÄÍâ¹Ø¼ü×Ö£¬ÔòSÖÐÿһ¸öÔª×éÔÚMÉϵÄÖµ±ØÐëÊÇ¿ÕÖµ»òÊǶÔÓ¦¹ØϵZÖÐij¸öÔª×éµÄÖ÷¹Ø¼ü×ÖÖµ¡£²ÎÕÕÍêÕûÐÔÊÇÊý¾Ý¿âÉè¼ÆÖÐÒ»¸öÖØÒªµÄ¸ÅÄî¡£ÔÚϵͳ²»Í¬µÄ±íÖУ¬µ±Êý¾Ý¿âËùÓвÎÕպϷ¨»ò·ÇºÏ·¨¹ØÁªÊ±¶¼»áÉæ¼°µ½²ÎÕÕÍêÕûÐÔ¡£µ±²ÎÕÕÍêÕûÐÔ´æÔÚʱ£¬ÈκÎÓë²»´æÔڵļǼ²úÉú¹ØÁª½«±äµÃÎÞЧ£¬ÓÉ´Ë¿É·ÀÖ¹Óû§³öÏÖ¸÷ÖÖ´íÎ󣬴ӶøÌṩ¸üΪ׼ȷºÍʵÓõÄÊý¾Ý¿â¡£
Íâ¼ü(foreign key):Èç¹û±íAµÄÖ÷¹Ø¼ü×ÖÊDZíBÖеÄ×ֶΣ¬Ôò¸Ã×ֶγÆΪ±íBµÄÍâ¼ü£¬±íA³ÆΪÖ÷±í£¬±íB³ÆΪ´Ó±í¡£Íâ¼üÊÇÓÃÀ´ÊµÏÖ²ÎÕÕÍêÕûÐԵģ¬²»Í¬µÄÍâ¼üÔ¼Êø·½Ê½½«¿ÉÒÔʹÁ½Õűí½ôÃܵĽáºÏÆðÀ´£¬ÌرðÊÇÐ޸ĻòÕßɾ³ýµÄ¼¶Áª²Ù×÷½«Ê¹µÃÈÕ³£µÄά»¤¹¤×÷¸ü¼ÓÇáËÉ¡£
¶þ¡¢MySQLÖеÄÍâ¼üʹÓÃÌõ¼þ
MySQLÒÔÍù²¢²»Ö§³ÖÍâ¼ü£¬ÔÒòÊÇÕâÖÖÖ§³Ö»á½µµÍËٶȺÍÐÔÄÜ¡£
ÔÚMySQL 3.23.44°æ±¾ºó£¬InnoDBÒýÇæÀàÐ͵ıíÖ§³ÖÁËÍâ¼üÔ¼Êø£¬µ«ÊÇĬÈÏÒýÇæMyISAMÄ¿Ç°»¹²»Ö§³ÖÍâ¼ü£¬Òò´ËʹÓÃʱÐèҪעÒ⽨Á¢Êý¾Ý¿â±íʱѡÔñInnoDB×÷ΪÊý¾Ý¿âÒýÇæ¡£
Íâ¼üµÄʹÓÃÌõ¼þ£º
1.Á½¸ö±í±ØÐëÊÇInnoDB±í£¬MyISAM±íÔÝʱ²»Ö§³ÖÍâ¼ü£¨¾Ý˵ÒÔºóµÄ°æ±¾ÓпÉÄÜÖ§³Ö£¬Ä¿Ç°ÎÒʹÓõÄÊÇ5.1.36»¹Ã»ÓÐÖ§³Ö£©£»
2.Íâ¼üÁбØÐ뽨Á¢ÁËË÷Òý£¬MySQL 4.1.2ÒÔºóµÄ°æ±¾ÔÚ½¨Á¢Íâ¼üʱ»á×Ô¶¯´´½¨Ë÷Òý£¬µ«Èç¹ûÔÚ½ÏÔçµÄ°æ±¾ÔòÐèÒªÏÔʾ½¨Á¢£»
3.Íâ¼ü¹ØϵµÄÁ½¸ö±íµÄÁбØÐëÊÇÊý¾ÝÀàÐÍÏàËÆ£¬Ò²¾ÍÊÇ¿ÉÒÔÏ໥ת»»ÀàÐ͵ÄÁУ¬±ÈÈçintºÍtinyint¿ÉÒÔ£¬¶øintºÍcharÔò²»¿ÉÒÔ£»
Èý¡¢Íâ¼üµÄÓï·¨
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
¸ÃÓï·¨¿ÉÒÔÔÚ CREATE TABLE ºÍ ALTER TABLE ʱʹÓã¬Èç¹û²»Ö¸¶¨CONSTRAINT symbol£¬MYSQL»á×Ô¶¯Éú³ÉÒ»¸öÃû×Ö¡£
ON DELETE¡¢ON UPDATE±íʾʼþ´¥·¢ÏÞÖÆ£¬¿ÉÉè²ÎÊý£º
RESTRICT£¨ÏÞÖÆÍâ±íÖеÄÍâ¼ü¸Ä¶¯£©
CASCADE£¨¸úËæÍâ¼ü¸Ä¶¯£©
SET NULL£¨Éè¿ÕÖµ£©
SET DEFAULT£¨ÉèĬÈÏÖµ£©
NO ACTION£¨ÎÞ¶¯×÷£¬Ä¬Èϵģ©
¾ßÌåʹÓÃʾÀý¿ÉÒÔ²
Ïà¹ØÎĵµ£º
Ò». Æô¶¯²ÎÊýÓÅ»¯
ÐÞ¸Ä my.cnf (»òÕßmy.ini)£¬¼ÓÈë/ÐÞ¸ÄÒÔϼ¸ÐÐ
#É趨»º´æµÄÁ¬½ÓÊý,½ÚÊ¡Á¬½ÓʱµÄ¿ªÏú
back_log = 64
#½ûÓÃÎļþϵͳÍⲿËø
external-locking = 0
#½ûÓÃBDB,Èç¹ûÄãȷʵ²»ÐèÒªµÄ»°,innodbÒ²ÊÇÈç´Ë
skip-bdb
#Ë÷Òý»º³å,Èç¹ûÊÇרÓõÄÊý¾Ý¿â·þÎñÆ÷,¿ÉÒÔÉèÖøߴï·þÎñÆ÷ÄÚ´æµÄÒ»°ë,Èç¹û²»ÊÇרÓõÄ,
#»¹ÊÇÉ ......
ŪÁËÒ»ÍíÉÏ£¬²éÁËn¶àÎÄÕ£¬³¢ÊÔÁËn¶à¸´Ôӵķ½·¨£¬×îºóÖÕÓÚÔÚһƪÎÄÕÂÖÐÕÒµ½ÁËÒ»¸ö³¬¼òµ¥µÄ·½·¨£¬ÕæÊÇ̤ÆÆÌúЬÎÞÃÙ´¦°¡£¡²»Óð´Õչܷ¨µÄ×ö·¨£¬Ö»Òª°´ÕÕÏÂÎĵķ½·¨±£¹ÜÄã¿ÉÒԳɹ¦ÅäÖÃtomcatµÄÁ¬½Ó³Ø¡£
½«tomcat/conf/context.xmlÎļþÖÃΪÒÔÏÂÄÚÈÝ¡£
<Context reloadable="true">
< ......
DELIMITER $$
DROP PROCEDURE IF EXISTS `hotel`.`prc_page_hotel_result`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `hotel`.`prc_page_hotel_result`(
in currpage int,
in columns varchar(500),
in tablename &n ......
ÓкܶàÅóÓÑËäÈ»°²×°ºÃÁËmysqlµ«È´²»ÖªÈçºÎʹÓÃËü¡£ÔÚÕâƪÎÄÕÂÖÐÎÒÃǾʹÓÁ¬½ÓMYSQL¡¢ÐÞ¸ÄÃÜÂë¡¢Ôö¼ÓÓû§µÈ·½ÃæÀ´Ñ§Ï°Ò»Ð©MYSQLµÄ³£ÓÃÃüÁî¡£
Ò»¡¢Á¬½ÓMYSQL¡£
¸ñʽ£º mysql -hÖ÷»úµØÖ· -uÓû§Ãû £pÓû§ÃÜÂë
1¡¢Àý1£ºÁ¬½Óµ½±¾»úÉϵÄMYSQL¡£
Ê×ÏÈÔÚ´ò¿ªDOS´°¿Ú£¬È»ºó½øÈëĿ¼ mysqlbin£¬ÔÙ¼üÈëÃüÁîmysql -uroot -p£¬»Ø³µ ......
³£³£Óöµ½´óÊý¾ÝÁ¿Æµ·±¶ÁдÊƱػᵼÖ·þÎñÆ÷µÄÐÔÄÜϽµ¡£ÏÂÃæ¼Ç¼һ϶ÔmysqlµÄÓÅ»¯ÅäÖÃ
1.Ê×ÏȹرÕauto commit¡£
2.¶ÔÓÚÊý¾Ý±íʹÓÃInnoDB¡£
3.¹Ø±ÕÍâ¼üÔ¼Êø¡£
4.²ÉÓöàÖµ²åÈë“INSERT INTO ±íÃû VALUES (1,2), (5,5), ...;” Èç¹ûÓдóÁ¿²¢·¢¿ÉÒÔÓøü¸ÄINSERT INTOΪ INSERT DELAYED INTO¿ÉÒÔÔڳ̶ÈÉϽ⾠......