mysql ×ÔÔöÁÐÏà¹ØÎÊÌâÕûÀí
MYSQL×ÔÔöÁÐ
1.¹Ø¼ü×Ö £ºauto_increment
2.×ÔÔöÓ÷¨
Àý:
CREATE TABLE animals ( id mediumint not null auto_increment,
name char(30) not null,
primary key (id));
3.¹ØÓÚ×ÔÔö
Q:Ôõô»ñµÃµ±Ç°µÄ×ÔÔöµÄ×î´óÖµ?
A:select @@identity
Q:Ôõô»ñµÃtableµÄµ±Ç°×ÔÔö×î´óÖµ?
A:select max(id) from table
Q:¶Ô×ÔÔöµÄÀí½â?
A: Ò»°ãÇé¿öÏ»ñÈ¡¸Õ²åÈëµÄÊý¾ÝµÄid£¬Ê¹ÓÃselect max(id) from table ÊÇ¿ÉÒԵġ£LAST_INSERT_ID ÊÇÓëtableÎ޹صģ¬Èç¹ûÏò±ía²åÈëÊý¾Ýºó£¬ÔÙÏò±íb²åÈëÊý¾Ý£¬LAST_INSERT_ID»á¸Ä±ä¡£
ʹÓõ¥INSERTÓï¾ä²åÈë¶àÌõ¼Ç¼, LAST_INSERT_ID·µ»ØÒ»¸öÁÐ±í¡£
@@identityÊDZíʾµÄÊÇ×î½üÒ»´ÎÏò¾ßÓÐidentityÊôÐÔ(¼´×ÔÔöÁÐ)µÄ±í²åÈëÊý¾Ýʱ¶ÔÓ¦µÄ×ÔÔöÁеÄÖµ£¬ÊÇϵͳ¶¨ÒåµÄÈ«¾Ö±äÁ¿¡£±ÈÈçÓиö±íA£¬ËüµÄ×ÔÔöÁÐÊÇid£¬µ±ÏòA±í²åÈëÒ»ÐÐÊý¾Ýºó£¬Èç¹û²åÈëÊý¾Ýºó×ÔÔöÁеÄÖµ×Ô¶¯Ôö¼ÓÖÁ101£¬Ôòͨ¹ýselect@@identityµÃµ½µÄÖµ¾ÍÊÇ101¡£
×¢:LAST_INSERT_IDÊÇÒ»¸öº¯Êý.
Ó÷¨:LAST_INSERT_ID()
Q:MYSQLÖеÄLAST_INSERT_ID()ºÍMSSQLÖеÄ@@IDENTITY
A:°´ÕÕÓ¦ÓÃÐèÒª£¬³£³£ÒªÈ¡µÃ¸Õ¸Õ²åÈëÊý¾Ý¿â±íÀïµÄ¼Ç¼µÄIDÖµ¡£
ÔÚMYSQLÖпÉÒÔʹÓÃLAST_INSERT_ID()º¯Êý£¬ÔÚMSSQLÖÐʹÓÃ@@IDENTITY¡£Í¦·½±ãµÄÒ»¸öº¯Êý¡£
µ«ÊÇ£¬ÕâÀïÐèҪעÒâµÄÊÇ£¬µ±Ê¹ÓÃINSERTÓï¾ä²åÈë¶àÌõ¼Ç¼µÄʱºò£¬Ê¹ÓÃLAST_INSERT_ID()·µ»ØµÄ»¹ÊǵÚÒ»ÌõµÄIDÖµ£¬¶ø@@IDENTITY·µ»Ø×îºóÒ»Ìõ¡£
Q:mysql_insert_id()ÓëLAST_INSERT_ID()
A:mysql_insert_id() ½« MySQL ÄÚ²¿µÄ C API º¯Êý mysql_insert_id() µÄ·µ»ØÖµ×ª»»³É long£¨PHPÖÐÃüÃûΪint£©¡£Èç¹û AUTO_INCREMENT µÄÁеÄÀàÐÍÊÇ BIGINT£¬Ôò mysql_insert_id() ·µ»ØµÄÖµ½«²»ÕýÈ·¡£¿ÉÒÔÔÚ SQL²éѯÖÐÓà MySQL ÄÚ²¿µÄ SQL º¯Êý LAST_INSERT_ID() À´Ìæ´ú¡£
MySQLµÄLAST_INSERT_ID()µÄ½éÉÜ mysql_insert_id()¾ÍÊǵ÷ÓÃLAST_INSERT_ID()À´ÊµÏֵġ£
ÔÚmysqlÖÐÓÃlast_insert_id()....ÔÚ³ÌÐòÖÐÓÃmysql_insert_id().
4.ÈçºÎÖØÖÃÖÖ×Óµ½1£º
truncate ÖØÖÃÖÖ×ÓÖµµ½“1” ¡£
5.select * from table_name where auto_increment_col_name is NULL »ñµÃ×îºó²åÈëµÄµÚÒ»ÐÐÊý¾Ý£»¼´Ê¹Ò»´ÎÐÔ²å´óÅúÊý¾ÝÒ²ÊÇÖ»·µ»Ø¸ÃÅú´ÎµÚÒ»ÐÐ×ÔÔöºóµÄÖµ£¡
ÌØÊâµÄ£º¶ÔÓÚmyisam±í£¬ÌØÊâµÄ¶ÔÓÚmyisamÒýÇæµÄ±í£¬Äã¿ÉÒÔÔÚÒ»¸ö¶àÁÐË÷ÒýÉϵĵڶþÁÐÉ϶¨Òå×ÔÔöÁУº½«ÔÚµÚÒ»Ë÷ÒýÁÐÉϲúÉú×ÔÔö£¬¿ÉÒÔÀí½âΪ£ºÔÚµÚÒ»Ë÷ÒýÁÐÉÏ·Ö×飬¼ÆËã¸Ã×éÉϵÄ×î´óÖµ£¬+1¡£¸ÃÐ
Ïà¹ØÎĵµ£º
mysql
·þÎñÆ÷ÖÐÓÐÁù¸ö¹Ø¼üλÖÃʹÓÃÁË×Ö·û¼¯µÄ¸ÅÄî,
ËûÃÇÊÇ£º
client
¡¢connection
¡¢database
¡¢results
¡¢server,
a
¡¢ÆäÖÐclient
Êǿͻ§¶ËʹÓõÄ×Ö·û¼¯£¬Ï൱ÓÚÍøÒ³ÖеÄ×Ö·û¼¯ÉèÖÃÈçÏÂ
<
meta http-equiv
=
"Content-Type"
content
=
"text/html;charset=utf ......
ÕâЩº¯Êý¶¼ÊÇMySQL×Ô´øµÄ£¬¿ÉÒÔÖ±½ÓʹÓÃÔÚPHPдµÄMySQL²éѯÓï¾äÖÐŶ
1-CURDATE()»òCURRENT_DATE()ºÍCURTIME()»òCURRENT_TIME()
ÕâÁ½¸öº¯ÊýÊDZȽϳ£Óõ½µÄ£¬¹ËÃû˼Ò壬µÚÒ»¸ö·µ»Øµ±Ç°ÈÕÆÚ£¬µÚ¶þ¸ö·µ»Øµ±Ç°Ê±¼ä
¿ÉÒÔÔÚMySQL¿Í»§¶Ë»òÕßPHPÓï¾äÀïд"SELECT CURDATE(),CURTIME()"²é¿´Ïà¹ØÐÅÏ¢.
»¹ÓÐÒ»¸öºÜÖØÒªµÄº¯ ......
mysqldump -u root -p -e --max_allowed_packet=1048576 --net_buffer_length=16384 msiptv>c:\thedump.sql
ÎÊÌ⣺ÔÚʹÓÃmysql¹ý³ÌÖб¸·ÝÊý¾Ý£¬Ò»°ã¶¼ÊDZ»·Ö³ÉsqlÓï¾äÎı¾£¬µ«mysqldumpÃüÁîĬÈÏÊǷdz£»úеµØ´¦ÀíÓï¾äµÄ×éÖ¯¡£µ¼ÖÂÖØÐµ¼ÈëµÄʱºòЧÂÊÌ«µÍ¡£±ÈÈç10ÍòÌõÊý¾Ý¾ÍÒªÖ´ÐÐ10Íò´ÎµÄsqlÓï¾ä£¬ºÄʱ̫¶à¡£
ÔÀí£º
......
·ÖÒ³¹¦ÄÜÔÚÈκÎÓ¦ÓÃÖж¼±È½Ï³£¼û£¬¶ø
Mysql
µÄ´æ´¢¹ý³Ì·ÖÒ³±ØÐëͨ¹ý¶¯Ì¬
sql
À´Ö´ÐС£·ÖÒ³¶ÔÓ¦µÄ
offset
ºÍ
row_count
±ØÐëÏÈÓÃ
concat
º¯Êý±ä³É×Ö·û´®×é×°µ½
sql
ÖУ¨ÈçÓï¾ä
1
£©£¬¶ø²»ÄÜÖ±½ÓʹÓã¨ÈçÓï¾ä
2
£¬
Mysql
²»Ö§³Ö£©¡£
Mysql
·ÖÒ³¹¦ÄܵÄʵÏÖÈçÏ´úÂëËùʾ£º
&nbs ......
Ïë²âÊÔϱí·ÖÇø¹¦Äܸú×ÔÖ÷µÄ·Ö²¼Ê½µÄÐÔÄܲîÒ죬½øÐÐÁ˼òµ¥µÄ²âÊÔ£¬Ç°ºó»¨·ÑÁ˰ë¸öСʱ£¬¿ÉÄܲâÊÔÊý¾Ý²»Ì«×¼È·¡£
¡¾²âÊÔ»·¾³¡¿
²Ù×÷ϵͳ£ºWindows XP SP2
MySQL£º 5.1.19-beta-community-nt-debug
¡¾²âÊÔ¹ý³Ì¡¿
²é¿´MySQL°æ±¾£º
mysql> select version();
+--------------------------------+
| version() ......