×¢ÒâMysqlÊý¾Ý½Ø¶Ï
Beware of MySQL Data Truncation
http://www.mysqlperformanceblog.com/2009/02/07/beware-of-mysql-data-truncation/
±ÈÈ磺ÓÐÒ»¸ö±íaritcleºÍÁíÒ»¸ö±íarticle_comment£¬¹ØÁªÊÇarticleµÄid
CREATE TABLE `article` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `article_comment` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(200) NOT NULL,
`article_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `art_id` (`article_id`),
CONSTRAINT `art_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
set sql_mode='';
insert into article values(12345678901,'name1');
insert into article_comment(text,article_id) values('text1',12345678901);
insert into article_comment(text,article_id) values('text2',12345678902);
²é¿´Êý¾Ý£º
article±í
4294967295 name1
article_comment±í
1 text1 4294967295
2 text2 4294967295
´ÓÖпÉÒÔ¿´³ö£¬±¾À´µÚ¶þ¸ö²åÈëµÄÆÀÂÛÏë¹ØÁªÁíÒ»¸öÎÄÕ£¬µ«ÊÇÈ´¹ØÁªµ½Á˵ÚһƪÎÄÕ£¬ÕâÊÇÒòΪMysqlµÄData Truncation
show warningsÏÔʾ
Warning | 1265 | Data truncated for column 'article_id' at row 1
Õâ»áÔì³É£º
£¨1£©ÆÀÂÛ¹ØÁªµ½´íÎóµÄÎÄÕÂ
£¨2£©Í¬Ò»ÆªÎÄÕ¹ØÁªµ½Ðí¶àµÄÆÀÂÛ£¨Õâ»áÔì³ÉÐÔÄÜÎÊÌ⣩
ÔõÑù½â¾öÄØ£¿
set sql_mode='STRICT_ALL_TABLES';
insert into article_comment(text,article_id) values('text1',12345678903);
Õâ»á±¨´í£º
[SQL] insert into article_comment(text,article_id) values('text1',12345678903);
[Err] 1264 - Out of range value for column 'article_id' at row 1
Òò´ËÒ²¾Í±ÜÃâÁËÉÏÊöÎÊÌâ¡£
Ïà¹ØÎĵµ£º
1 MySQLÖ§³Öenum,ºÍsetÀàÐÍ£¬SQL Server²»Ö§³Ö
¡¡¡¡2 MySQL²»Ö§³Önchar,nvarchar,ntextÀàÐÍ
¡¡¡¡3 MySQLµÄµÝÔöÓï¾äÊÇAUTO_INCREMENT£¬¶øMS SQLÊÇidentity(1,1)
¡¡¡¡4 MS SQLĬÈϵ½´¦±í´´½¨Óï¾äµÄĬÈÏÖµ±íʾÊÇ((0)),¶øÔÚMySQLÀïÃæÊDz»ÔÊÐí´øÁ½À¨ºÅµÄ
¡¡¡¡5 MySQLÐèҪΪ±íÖ¸¶¨´æ´¢ÀàÐÍ
¡¡¡¡6 MS SQLʶ±ð·ûÊÇ[],[type]±íÊ ......
#ÐÂÔöÒ»¸ö×ֶΣ¬Ä¬ÈÏֵΪ0£¬·Ç¿Õ£¬×Ô¶¯Ôö³¤£¬Ö÷¼ü
alter table tabelname add new_field_name field_type default 0 not null auto_increment ,add primary key (new_field_name);
#Ôö¼ÓÒ»¸öÐÂ×Ö¶Î
alter table tableName ......
ÔÚMySQLÖУ¬¶ÔÓÚË÷ÒýµÄʹÓò¢ÊÇÒ»Ö±¶¼²ÉÓÃÕýÈ·µÄ¾ö¶¨¡£
¡¡¡¡¼òµ¥±íµÄʾÀý£º
¡¡¡¡create TABLE `r2` (
¡¡¡¡ID` int(11) DEFAULT NULL,
¡¡¡¡ID1` int(11) DEFAULT NULL,
¡¡¡¡CNAME` varchar(32) DEFAULT NULL,
¡¡¡¡KEY `ID1` (`ID1`)
¡¡¡¡) ENGINE=MyISAM DEFAULT charSET=latin1
¡¡¡¡select count(*) from r2;
¡¡ ......
http://book.51cto.com/art/200803/68118.htm
ÕªÒª£º¡¶ÉîÈëdz³öMySQL——Êý¾Ý¿â¿ª·¢¡¢ÓÅ»¯Óë¹ÜÀíά»¤¡·´ÓÊý¾Ý¿âµÄ»ù´¡¡¢¿ª·¢¡¢ÓÅ»¯¡¢¹ÜÀí4·½Ãæ¶ÔMySQL½øÐÐÁËÏêϸµÄ½éÉÜ£¬ÆäÖÐÿһ²¿·Ö¶¼¶ÀÁ¢³Éƪ£¬Ã¿Ò»ÆªÓÖ°üÀ¨¶à¸öÕ½ڡ£±¾ÊéÃæÏòʵÓã¬ÄÚÈݸ²¸Ç¹ã·º£¬½²½âÓÉdzÈëÉÊʺÏÓÚ¸÷¸ö²ã´ÎµÄ¶ÁÕß¡£
µÚ20ÕÂ ËøÎÊÌâ
ËøÊÇ ......
Twitter¹«Ë¾Ò»Î»Ãû½ÐRyan KingµÄ¹¤³ÌʦÈÕÇ°Ïò²©¿ÍMyNoSQL͸¶£¬¹«Ë¾¼Æ»®´ÓMySQLǨÒƵ½CassandraÊý¾Ý¿â£¬ÒòΪºóÕß¾ßÓиü´óµÄµ¯ÐÔ¡¢¿ÉÀ©Õ¹ÐԺʹóÁ¿µÄÉçÇøÍøÂ翪Դ¿ª·¢ÈËÔ±¡£
ÎÒÃÇÓдóÁ¿µÄÊý¾Ý£¬ÔÚÊý¾Ý¾Þ´ó£¬Ôö³¤ÂÊÕýÔÚ¼ÓËÙµÄÇé¿öÏ£¬ÎÒÃÇÐèÒªÒ»¸öϵͳ£¬Ëü¿ÉÒÔ¸üΪ×Ô¶¯»¯£¬²¢¸ß¶È¿É¿¿¡¢¿ÉÓá£Ryan K ......