使用Mysql的一些细节

1.主键id字段一般建成BIGINT类型，后面的长度定义只是一个预设值，如果到达那个预设长度，数据库会自动增加那个长度，也就是BIGINT(20)和BIGINT(24)都能达到BIGINT的最大值，INT(11)和INT(14)也都能达到INT的最大值，但是INT无法自动转化成BIGINT,这个和VARCHAR不一样，后者在达到一定长度会被认为另外一种文本数据类型。


2.做一些数据复制的时候，有时候会出现一些binlog的问题，那这个时候可能会去做一些binlog解析工作查看binlog文件内容具体是什么。那么可以用mysql自带的mysqlbinlog在命令行中离线解析并输出到文件。

D:\tools\MySQLX\MySQL Server 5.1\bin>mysqlbinlog.exe  --verbose  --database=product  D:\mysql-bin.004080 >d:\mysql-bin-a.txt.

binlog内容如下

...
BEGIN
/*!*/;
# at 31221275
# at 31221403
#111024 19:06:22 server id 3  end_log_pos 31221403 	Table_map: `product`.`product` mapped to number 223
#111024 19:06:22 server id 3  end_log_pos 31222575 	Update_rows: table id 223 flags: STMT_END_F

BINLOG '
rkalThMDAAAAgAAAAJtm3AEAAN8AAAAAAAAAC2JiY19wcm9kdWN0AAtiYmNfcHJvZHVjdAAoCA8D
AwMDAgMPDw8IDw8PAQ8DDwEDDwMICAgPAgMMAQgMDAgPDwgPDBwAAkwEQAYAAjwAKAAAAYAAAAJA
HwABLAEAAkAfAA8hgPw=
rkalThgDAAAAlAQAAC9r3AEQAN8AAAAAAAEAKP////////////8AAACAoKaGAQAAAAAADAC7qs6q
NTU4OC1TS1U8hgEAMDgBADA4AQA8hgEAMgBmAAAAGgExMDAwMDoxMDAwMDsxMDAwMToxMDAwNzsx
MDAwMjoxMDAxMzsxMDAwMzoxMDAyMDsxMDAwNDoxMDAyMzsxMDA3MDozMjI5MTExOzIwMDAwOjEx
ODEzOzIwNTcxOjI4OTU0OzIwNTcxOjMyMzEzNDg7MjA1NzM6MzA1OTE7MjA1NzQ6Mjg5NzM7MjA1
NzQ6Mjc4Nzc7MjA1NzQ6MzIyNDYzMjsyMDcxMDoyMTk1OTsyMDg3OToyMTQ1NjsyMDkzMDozMzAw
MDsyMTUxNDo0MjM3NTszMDYwNjoxMTIwMzA7MTYyNzA5OTo5MDYxNjsxNjI3MjA3OjMyMzI0ODQ7
MTYyNzIwNzoyODMyOTsxNjMwNjk2OjMyODQ1NzAAACAAaTIvVDFOcGRYWFhYOXV0SjBYdlEyXzA0
NDExMi5qcGcQAAAAAAAAAAa62sH6va0Guf62+7H1FgBUMWpvUmJYZEpjWFhib3NwalguZHBj+AAA
AAAACgB0YnRlc3QxNDIyBAAAAAAAABEAAACjhgEAAAAAAOgFAAAAAAAA9wMAAAAAAAAJADE3NTc1
NjQ3NwEAoi0CAFYa+sdFEgAAAMu7drtFEgAAjfVN00USAAAAAAAAAAAAABcAILL6xrfL+cr0uanT
psnMtcS5q8u+w/sRADE1MTIgMTEgOSA4IDcgNiA1AQA7AAAAgKCmhgEAAAAAAAwAu6rOqjU1ODgt
U0tVPIYBADA4AQAwOAEAPIYBADIAZgAAABoBMTAwMDA6MTAwMDA7MTAwMDE6MTAwMDc7MTAwMDI6
MTAwMTM7MTAwMDM6MTAwMjA7MTAwMDQ6MTAwMjM7MTAwNzA6MzIyOTExMTsyMDAwMDoxMTgxMzsy
MDU3MToyODk1NDsyMDU3MTozMjMxMzQ4OzIwNTczOjMwNTkxOzIwNTc0OjI4OTczOzIwNTc0OjI3
ODc3OzIwNTc0OjMyMjQ2MzI7MjA3MTA6MjE5NTk7MjA4Nzk6MjE0NTY7MjA5MzA6MzMwMDA7MjE1
MTQ6NDIzNzU7MzA2MDY6MTEyMDMwOzE2MjcwOTk6OTA2MTY7MTYyNzIwNzozMjMyNDg0OzE2Mjcy
MDc6MjgzMjk7MTYzMDY5NjozMjg0NTcwBADCzMmrIABpMi9UMU5wZFhYWFg5dXRKMFh2UTJfMDQ0
MTEyLmpwZxAAAAAAAAAABrrawfq9rQa5/rb7sfUWAFQxam9SYlhkSmNYWGJvc3BqWC5kcGP4AAAA
AAAKAHRidGVzdDE0MjIEAAAAAAAAEQAAAKOGAQAAAAAA6AUAAAAAAAD3AwAAAAAAAAkAMTc1NzU2
NDc3AQCiLQIAVhr6x0USAAAAy7t2u0USAACN9U3TRRIAAAAAAAAAAAAAFwAgsvrGt8v5yvS5qdOm
ycy1xLmry77D+xEAMTUxMiAxMSA5IDggNyA2IDUBADs=
'/*!*/;
### UPDATE bbc_product.bbc_product
### WHERE
###   @1=100006
###   @2='华为5588-SKU'
###   @3=99900
###   @4=79920
###   @5=79920
###   @6=99900
###   @7=50
###   @8=102
###   @9='10000:10000;10001:10007;10002:10013;10003:10020;10004:10023;10070:3229111;20000:11813;20571:28954;20571:3231348;20573:30591;20574:28973;20574:27877;20574:3224632;20710:21959;20879:21456;20930:33000;21514:42375;30606:112030;1627099:90616;1627207:3232484;1627207:28329;1630696:3284570'
###   @10=''
###   @11='i2/T1NpdXXXX9utJ0XvQ2_044112.jpg'
###   @12=16
###   @13='黑龙江'
###   @14='哈尔滨'
###   @15='T1joRbXdJcXXbospjX.dpc'
###   @16=-8 (248)
###   @17=''
###   @18=0
###   @19='test1422'
###   @20=4
###   @21=0
###   @22=''
###   @23=17
###   @24=100003
###   @25=1512
###   @26=1015
###   @27='175756477'
###   @28=1
###   @29=142754
###   @30=2009-09-17 10:01:18
###   @31=0
###   @32=NULL
###   @33=2009-07-07 16:41:07
###   @34=2009-11-07 14:51:01
###   @35=0
###   @36=' 产品所属供应商的公司名'
###   @37='1512 11 9 8 7 6 5'
###   @38=NULL
###   @39=';'
###   @40=NULL
### SET
###   @1=100006
###   @2='华为5588-SKU'
###   @3=99900
###   @4=79920
###   @5=79920
###   @6=99900
###   @7=50
###   @8=102
###   @9='10000:10000;10001:10007;10002:10013;10003:10020;10004:10023;10070:3229111;20000:11813;20571:28954;20571:3231348;20573:30591;20574:28973;20574:27877;20574:3224632;20710:21959;20879:21456;20930:33000;21514:42375;30606:112030;1627099:90616;1627207:3232484;1627207:28329;1630696:3284570'
###   @10='绿色'
###   @11='i2/T1NpdXXXX9utJ0XvQ2_044112.jpg'
###   @12=16
###   @13='黑龙江'
###   @14='哈尔滨'
###   @15='T1joRbXdJcXXbospjX.dpc'
###   @16=-8 (248)
###   @17=''
###   @18=0
###   @19='test1422'
###   @20=4
###   @21=0
###   @22=''
###   @23=17
###   @24=100003
###   @25=1512
###   @26=1015
###   @27='175756477'
###   @28=1
###   @29=142754
###   @30=2009-09-17 10:01:18
###   @31=0
###   @32=NULL
###   @33=2009-07-07 16:41:07
###   @34=2009-11-07 14:51:01
###   @35=0
###   @36=' 产品所属供应商的公司名'
###   @37='1512 11 9 8 7 6 5'
###   @38=NULL
###   @39=';'
###   @40=NULL
# at 31222575
#111024 19:06:22 server id 3  end_log_pos 31222602 	Xid = 6400552307
COMMIT/*!*/;
# at 31222602
#111024 19:06:22 server id 3  end_log_pos 31222679 	Query	thread_id=5886165	exec_time=0	error_code=0
SET TIMESTAMP=1319454382/*!*/;
SET @@session.sql_mode=2097152/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=28/*!*/;
...