mysqlÖ®ÄÚ´æ±í
ÈçºÎ´´½¨ÄÚ´æ±í
£¿
´´½¨ÄÚ´æ±í·Ç³£µÄ¼òµ¥£¬Ö»Ðè×¢Ã÷ ENGINE=
MEMORY ¼´¿É:
CREATE TABLE `tablename` ( `columnName` varchar(256) NOT NUL)
ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=100000000;
×¢Òâ
£º
µ±ÄÚ´æ±íÖеÄÊý¾Ý´óÓÚmax_heap_table_sizeÉ趨µÄÈÝÁ¿´óСʱ£¬mysql»áת»»³¬³öµÄÊý¾Ý´æ´¢µ½´ÅÅÌÉÏ£¬Òò´ËÕâÊÇÐÔÄܾʹó´òÕÛ¿ÛÁË£¬Ëù
ÒÔÎÒÃÇ»¹ÐèÒª¸ù¾ÝÎÒÃǵÄʵ¼ÊÇé¿öµ÷Õûmax_heap_table_size£¬ÀýÈçÔÚ.cnfÎļþÖÐ[mysqld]µÄÏÂÃæ¼ÓÈ룺
max_heap_table_size = 2048M
ÁíÍâÔÚ½¨±íÓï¾äÖл¹¿ÉÒÔͨ¹ýMAX_ROWSÀ´¿ØÖƱíµÄ¼Ç¼Êý¡£
ÄÚ´æ±íʹÓùþϣɢÁÐË÷Òý°ÑÊý¾Ý±£´æÔÚÄÚ´æÖУ¬Òò´Ë¾ßÓм«¿ìµÄËٶȣ¬Êʺϻº´æÖÐСÐÍÊý¾Ý¿â£¬µ«ÊÇʹÓÃÉÏÊܵ½Ò»Ð©ÏÞÖÆ£¬ÒÔÏÂÊÇÀ¶²ÝʹÓõÄһЩ¸ÐÊÜ¡£
1¡¢heap¶ÔËùÓÐÓû§µÄÁ¬½ÓÊǿɼûµÄ£¬ÕâʹµÃËü·Ç³£ÊʺÏ×ö»º´æ¡£
2¡¢×¢ÒâÊÂÏî¡£
heap²»ÔÊÐíʹÓÃxxxTEXTºÍxxxBLOBÊý¾ÝÀàÐÍ£»Ö»ÔÊÐíʹÓÃ=ºÍ<=>²Ù×÷·ûÀ´ËÑË÷¼Ç¼£¨²»ÔÊÐí<¡¢>¡¢<=»ò>=£©£»²»Ö§³Öauto_increment£»Ö»ÔÊÐí¶Ô·Ç¿ÕÊý¾ÝÁнøÐÐË÷Òý£¨not
null£©¡£
×¢£º²Ù×÷·û “<=>” ˵Ã÷£ºNULL-safe
equal.Õâ¸ö²Ù×÷·ûºÍ“=”²Ù×÷·ûÖ´ÐÐÏàͬµÄ±È½Ï²Ù×÷£¬²»¹ýÔÚÁ½¸ö²Ù×÷Âë¾ùΪNULLʱ£¬ÆäËùµÃֵΪ1¶ø²»ÎªNULL£¬¶øµ±Ò»¸ö²Ù×÷ÂëΪNULLʱ£¬ÆäËùµÃֵΪ0¶ø²»ÎªNULL¡£
3¡¢·þÎñÆ÷ÖØÆô£¬ËùÓÐheap±íÊý¾Ý¶ªÊ§
£¬µ«ÊÇheap±í½á¹¹ÈÔÈ»´æÔÚ£¬ÒòΪheap±í½á¹¹ÊÇ´æ·ÅÔÚʵ¼ÊÊý¾Ý¿â·¾¶Ïµģ¬²»»á×Ô¶¯É¾³ý¡£ÖØÆôÖ®ºó£¬heap½«±»Çå¿Õ£¬Õâʱºò¶ÔheapµÄ²éѯ½á¹û¶¼Êǿյġ£
¶ÔÓÚÖØÆôÔì³ÉµÄÊý¾Ý¶ªÊ§£¬ÓÐÒÔϵĽâ¾ö°ì·¨
£º
¡¡a¡¢ÔÚÈκβéѯ֮ǰ£¬Ö´ÐÐÒ»´Î¼òµ¥µÄ²éѯ£¬ÅжÏheap±íÊÇ·ñ´æÔÚÊý¾Ý£¬Èç¹û²»´æÔÚ£¬Ôò°ÑÊý¾ÝÖØÐÂдÈ룬»òÕßDROP±íÖØи´ÖÆijÕÅ±í¡£ÕâÐèÒª¶à×öÒ»´Î²éѯ¡£²»¹ý¿ÉÒÔд³ÉincludeÎļþ£¬ÔÚÐèÒªÓøÃheap±íµÄÒ³ÃæËæʱµ÷Ó㬱ȽϷ½±ã¡£
¡¡b¡¢¶ÔÓÚÐèÒª¸Ãheap±íµÄÒ³Ã棬ÔÚ¸ÃÒ³ÃæµÚÒ»´ÎÇÒ½öÔÚµÚÒ»´Î²éѯ¸Ã±íʱ£¬¶ÔÊý¾Ý¼¯½á¹û½øÐÐÅжϣ¬Èç¹û½á¹ûΪ¿Õ£¬ÔòÐèÒªÖØÐÂдÈëÊý¾Ý¡£ÕâÑù¿ÉÒÔ½ÚÊ¡Ò»´Î²éѯ¡£
¡¡c¡¢¸üºÃµÄ°ì·¨ÊÇÔÚmysqlÿ´ÎÖØÐÂÆô¶¯Ê±×Ô¶¯Ð´ÈëÊý¾Ýµ½heap£¬µ«ÊÇÐèÒªÅäÖ÷þÎñÆ÷£¬¹ý³Ì±È½Ï¸´ÔÓ£¬Í¨ÓÃÐÔÊܵ½ÏÞÖÆ¡£
4¡¢Èç¹ûheapÊǸ´ÖƵÄijÊý¾Ý±í£¬Ôò¸´ÖÆÖ®ºóËùÓÐÖ÷¼ü¡¢Ë÷Òý¡¢×ÔÔöµÈ¸ñʽ½«²»¸
Ïà¹ØÎĵµ£º
Ò»¡¢
¿ØÖÆÁ÷³Ìº¯Êý
a)
CASE WHEN THEN
º¯Êý
Óï·¨£º
CASE value WHEN [compare-value]
THEN result [WHEN [compare-value] THEN result ……] [ELSE result ] END CASE WHEN
[condition] THEN result [WHEN[condition] THEN result …… ......
//Ö÷¼ü
alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);
//Ôö¼ÓÒ»¸öÐÂÁÐ
alter table t2 add d timestamp;
alter table infos add ex tinyint not null default '0';
//ɾ³ýÁÐ
alter table t2 drop column c;
//ÖØÃüÃûÁÐ
......
¾³£Óõ½£¬µ«ÊÇ×ÜÊǼDz»×¡¡£
Óï·¨ÈçÏ£º
ALTER TABLE JOB ADD CONSTRAINT fk_prono
FOREIGN KEY(PRONO)
REFERENCES PROJECT(PRONO)
ON DELETE CASCADE
ON UPDATE NO ACTION;
ÎÒÔÚmysqlÖÐÌí¼ÓÁËÍâ¼üÔ¼Êø£¬ÊÔÁË°ëÌìû³É¹¦£¬ºóÀ´°ÑÍâ¼üµÄÃû×ÖÓÉFK¸Ä³ÉFK_1¾ÍºÃÁË£¬¿ÉÄÜmysqlÖÐFKÊǸö±£Áô×Ö£¬×¢Ò⣡ ......
ÄÚÈÝ£º
*************
1
°²×°£Í£Ù£Ó£Ñ£Ìºó¸ü¸ÄÁˣңϣϣԵÄÃÜÂëºóÓÃ
net startmysql
Æô¶¯Ê±ÎÒ¾ÍÓöµ½ÁËÕâÑùµÄÎÊÌ⣮ʹÓÃÒÔÏÂÃüÁîºó
c:\mysql\bin\mysqladmin-u root -p shutdown
ÔÙnet start mysql¾ÍûÓÐÕâ¸ö´íÎóÌáʾÁË!
*************
2
MySQLµÄ1067´íÎó
Q£ºÎÒµÄMysqlÅöµ½ÁË 1067 ´íÎó
´íÎóÐÅϢΪ£º
......
--default-character-set=utf8 ÓëÐÞ¸ÄsqlÎļþ¼ÓÈ룺SET NAMES 'utf8';
µ¼Èë½Å±¾Ê±£¬SET NAMES 'xxx' £¬xxxÓ¦¸ÃºÍsql½Å±¾ÎļþµÄʵ¼Ê±àÂëÒ»Ö£¬Èç¹ûÄãÉèÖõÄʱutf-8 £¬ÄÇôÇëÈ·ÈÏÄãµÄsql½Å±¾Îļþʱutf-8±£´æµÄ:
iconv -f UTF-8 xxx.sql
Èç¹ûÏÔʾÕý³££¬ÄÇôÕâÒ»²½¾ÍOKÁË
ÁíÍâ×¢ÒâÄãµÄ½Å±¾ÀÓÐûÓиø¿â±íÉèÖ ......