mysql BLOB型別 TEXT型別

MySQL的四種BLOB和TEXT型別:
　　

BLOB型別	大小（位元組）	TEXT型別	大小（字元個數）
TINYBLOB	255 (28 – 1)	TINYTEXT	255  char
BLOB	65,535 (216 – 1)	TEXT	65,535
MEDIUMBLOB	16,777,215 (224 – 1)	MEDIUMTEXT	16,777,215
LONGBLOB	4GB (232 – 1)	LONGTEXT	4GB

•  TINYBLOB

  A BLOB column with a maximum length of 255 (2⁸ – 1) bytes. Each TINYBLOB value is stored using a 1-byte length prefix that indicates the number of bytes in the value.

•  TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 255 (2⁸ – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value.

•  BLOB[(M)]

  A BLOB column with a maximum length of 65,535 (2¹⁶ – 1) bytes. Each BLOB value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

  An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest BLOBtype large enough to hold values M bytes long.

•  TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 65,535 (2¹⁶ – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

  An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXTtype large enough to hold values M characters long.

•  MEDIUMBLOB

  A BLOB column with a maximum length of 16,777,215 (2²⁴ – 1) bytes. Each MEDIUMBLOB value is stored using a 3-byte length prefix that indicates the number of bytes in the value.

•  MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 16,777,215 (2²⁴ – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.

•  LONGBLOB

  A BLOB column with a maximum length of 4,294,967,295 or 4GB (2³² – 1) bytes. The effective maximum length ofLONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

•  LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 4,294,967,295 or 4GB (2³² – 1) characters. The effective maximum length is less if the value contains multi-byte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. EachLONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

•  TINYBLOB

  A BLOB column with a maximum length of 255 (2⁸ – 1) bytes. Each TINYBLOB value is stored using a 1-byte length prefix that indicates the number of bytes in the value.

•  TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 255 (2⁸ – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value.

•  BLOB[(M)]

  A BLOB column with a maximum length of 65,535 (2¹⁶ – 1) bytes. Each BLOB value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

  An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest BLOBtype large enough to hold values M bytes long.

•  TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 65,535 (2¹⁶ – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

  An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXTtype large enough to hold values M characters long.

•  MEDIUMBLOB

  A BLOB column with a maximum length of 16,777,215 (2²⁴ – 1) bytes. Each MEDIUMBLOB value is stored using a 3-byte length prefix that indicates the number of bytes in the value.

•  MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 16,777,215 (2²⁴ – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.

•  LONGBLOB

  A BLOB column with a maximum length of 4,294,967,295 or 4GB (2³² – 1) bytes. The effective maximum length ofLONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

•  LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 4,294,967,295 or 4GB (2³² – 1) characters. The effective maximum length is less if the value contains multi-byte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. EachLONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

參考：http://dev.mysql.com/doc/refman/5.6/en/string-type-overview.html
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/195110/viewspace-1126460/，如需轉載，請註明出處，否則將追究法律責任。