MySQL 数据库 - 数据类型

创建时间:
2012-04-04 15:03
最近更新:
2018-08-14 17:20

约定

  • 不使用 显示宽度。例如 不使用 int(m) 而是使用 int

总结一

数值类型

  1. BIT[(M)] - 1-64
  2. BOOL, BOOLEAN - Tony 从不用 它 与 tinyint(1),而是用 bit(1)
  3. TINYINT[(M)] [UNSIGNED] [ZEROFILL] - -128-127 / 0-255
  4. SMALLINT[(M)] [UNSIGNED] [ZEROFILL] - -32768-32767 / 0-65535
  5. MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] - -8388608-8388607 / 0-16777215
  6. INT[(M)] [UNSIGNED] [ZEROFILL] - -2147483648-2147483647 / 0-4294967295
  7. INTEGER[(M)] [UNSIGNED] [ZEROFILL] - int 的同义词。Tony 从不用它。
  8. BIGINT[(M)] [UNSIGNED] [ZEROFILL] - -9223372036854775808-9223372036854775807 / 0-18446744073709551615 (TonyRemark: 与 .NET System.Int64/UInt64 取值范围一致)
  9. DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] - M 上限 65 忽略则默认为 10、D 上限 30 忽略则默认为 0
  10. DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] - decimal 的同义词。Tony 从不用它们。
  11. FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
  12. DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
  13. DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL] - double 的同义词。Tony 从不用它们。
  14. FLOAT(p) [UNSIGNED] [ZEROFILL] - floatdouble 的特殊语法。Tony 从不用它。

日期和时间类型

  1. DATE
  2. DATETIME[(fsp)]
  3. TIMESTAMP[(fsp)]
  4. TIME[(fsp)]
  5. YEAR[(4)]

字符串类型

  1. [NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] - 0-255。
  2. [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] - 0-65535。
  3. BINARY[(M)] - 0-255。
  4. VARBINARY(M) - 0-65535 (64KB)。小于 255 字节 则用它; 大于则用 BLOB。详见本文中 "varbinary vs blob"。
  5. TINYBLOB - 最多 255 字节。
  6. TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - 最多 255 字符。
  7. BLOB[(M)] - 最多 65535 字节。
  8. TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] - 最多 65535 字符。可存储字符数为 "万级"。用于存储短文章。
  9. MEDIUMBLOB - 最多 16,777,215 字节。
  10. MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - 最多 16,777,215 字符。可存储字符数为 "千万级"。用于存储长文章。
  11. LONGBLOB - 最多 4GB 字节。
  12. LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - 最多 4GB 字符。
  13. ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] - 为了兼容其它厂家的数据库,Tony 从不用它。
  14. SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] - 为了兼容其它厂家的数据库,Tony 从不用它。

总结二

数值类型

  1. bit BIT[(M)] - A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
  2. bool BOOL, BOOLEAN - These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true. Tony 从不用 它 与 tinyint(1),而是用 bit(1)
  3. tinyint TINYINT[(M)] [UNSIGNED] [ZEROFILL] - A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
  4. smallint SMALLINT[(M)] [UNSIGNED] [ZEROFILL] - A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
  5. mediumint MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] - A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
  6. int INT[(M)] [UNSIGNED] [ZEROFILL] - A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. INTEGER[(M)] [UNSIGNED] [ZEROFILL] is a synonym for INT.
  7. bigint BIGINT[(M)] [UNSIGNED] [ZEROFILL] - A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
  8. decimal DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] - M 上限 65 忽略则默认为 10、D 上限 30 忽略则默认为 0. A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10. UNSIGNED, if specified, disallows negative values. All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits. DEC, NUMERIC, FIXED are synonyms for DECIMAL. The FIXED synonym is available for compatibility with other database systems.
  9. float FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] - A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system. M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places. UNSIGNED, if specified, disallows negative values. Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. See Section B.5.4.7, “Solving Problems with No Matching Rows”.
  10. double DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] - A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system. M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places. UNSIGNED, if specified, disallows negative values. DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL] are synonyms for DOUBLE. Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.

日期和时间类型

  1. date DATE - A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but permits assignment of values to DATE columns using either strings or numbers.
  2. datetime DATETIME[(fsp)] - A date and time combination. The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS[.fraction]' format, but permits assignment of values to DATETIME columns using either strings or numbers. An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. Automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
  3. timestamp TIMESTAMP[(fsp)] - A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the “zero” TIMESTAMP value. An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
  4. time TIME[(fsp)] - A time. The range is '-838:59:59.000000' to '838:59:59.000000'. MySQL displays TIME values in 'HH:MM:SS[.fraction]' format, but permits assignment of values to TIME columns using either strings or numbers. An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
  5. year YEAR[(4)] - A year in four-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of values to YEAR columns using either strings or numbers. Values display as 1901 to 2155, and 0000. Note: The YEAR(2) data type is deprecated and support for it is removed in MySQL 5.7.5. To convert YEAR(2) columns to YEAR(4), see Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”.

字符串类型

  1. char [NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] - A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1. Trailing spaces are removed when CHAR values are retrieved unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled. MySQL permits you to create a column of type CHAR(0). This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value. CHAR(0) is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).
  2. varchar [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] - A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters. MySQL follows the standard SQL specification, and does not remove trailing spaces from VARCHAR values.
  3. binary BINARY[(M)] - 0-255. The BINARY type is similar to the CHAR type, but stores binary byte strings rather than nonbinary character strings. An optional length M represents the column length in bytes. If omitted, M defaults to 1.
  4. varbinary VARBINARY(M) - 0-65535 (64KB)。小于 255 字节 则用它; 大于则用 BLOB。The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the maximum column length in bytes.
  5. tinyblob TINYBLOB - 最多 255 字节. A BLOB column with a maximum length of 255 (2^8 − 1) bytes. Each TINYBLOB value is stored using a 1-byte length prefix that indicates the number of bytes in the value.
  6. tinytext TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - 最多 255 字符. A TEXT column with a maximum length of 255 (2^8 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value.
  7. blob BLOB[(M)] - 最多 65535 字节. A BLOB column with a maximum length of 65,535 (2^16 − 1) bytes. Each BLOB value is stored using a 2-byte length prefix that indicates the number of bytes in the value. 可以给出该类型的可选长度 M。如果给出,则 MySQL 将列创建为最小的但足以容纳 M 字节长的值的 blob 类型。
  8. text TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] - 最多 65535 字符。可存储字符数为 "万级"。用于存储短文章。A TEXT column with a maximum length of 65,535 (2^16 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value. 如果该值包含多字节字符 则有效最大长度较小。 可以给出该类型的可选长度 M。如果给出,则 MySQL 将列创建为最小的但足以容纳 M 字符长的值的 text 类型。
  9. mediumblob MEDIUMBLOB - 最多 16,777,215 字节。A BLOB column with a maximum length of 16,777,215 (2^24 − 1) bytes. Each MEDIUMBLOB value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
  10. mediumtext MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - 最多 16,777,215 字符。可存储字符数为 "千万级"。用于存储长文章。A TEXT column with a maximum length of 16,777,215 (2^24 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.
  11. longblob LONGBLOB - 最多 4GB 字节。A BLOB column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) bytes. The effective maximum length of LONGBLOB 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. 有效的最大的长度 取决于 客户端/服务器 协议中 配置的 最大包尺寸 和 可用的内存。
  12. longtext LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name] - 最多 4GB 字符。A TEXT column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value. 如果该值包含多字节字符 则有效最大长度较小。 有效的最大的长度 取决于 客户端/服务器 协议中 配置的 最大包尺寸 和 可用的内存。
  13. enum ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] - 为了兼容其它厂家的数据库,Tony 从不用它。An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. ENUM values are represented internally as integers. 只能有一个值的 字符串对象,该值选自 值列表、NULL、特殊的''错误值。enum 值在内部被表示为整数。 An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group. For more information on these limits, see Section C.10.5, “Limits Imposed by .frm File Structure”. enum 列 最多可以有 65,535 个不同的元素。
  14. set SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] - 为了兼容其它厂家的数据库,Tony 从不用它。A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... SET values are represented internally as integers. 可以有零个或多个值 的 字符串对象,每个值必须来自值列表。 set 值在内部被表示为整数。 A SET column can have a maximum of 64 distinct members. A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group. For more information on this limit, see Section C.10.5, “Limits Imposed by .frm File Structure”. set 列 最多可以有 64 个不同的成员。

精度/小数位/时间精度/最大字符数 统计记录

执行 select * from information_schema.columns where DATA_TYPE='xxx'; (将 'xxx' 替换为数据类型之一),观察结果中的相关列。统计结果如下

  • bit(1): NUMERIC_PRECISION = 1; NUMERIC_SCALE = null. BIT(M) - bit({p}).
  • tinyint: NUMERIC_PRECISION = 3; NUMERIC_SCALE = 0. This "3" is Display-Width. tinyint - tinyint.
  • int: NUMERIC_PRECISION = 10; NUMERIC_SCALE = 0. This "10" is Display-Width. int - int.
  • bitint: NUMERIC_PRECISION = signed/19/bigint(20) or unsigned/20/bigint(21); NUMERIC_SCALE = 0. This "19/20" is Display-Width. bigint - bigint.
  • decimal: NUMERIC_PRECISION = 4 - 65; NUMERIC_SCALE = 0 - 6. DECIMAL(M,D) - decimal({p},{s}).
  • float: NUMERIC_PRECISION = 3 - 12; NUMERIC_SCALE = 1 - 2. FLOAT(M,D) - float({p},{s}).
  • double: NUMERIC_PRECISION = 10 - 12; NUMERIC_SCALE = null - 3. DOUBLE(M,D) - double({p},{s}).
  • datetime: NUMERIC_PRECISION = null; NUMERIC_SCALE = null; DATETIME_PRECISION = 0 - 2. DATETIME(fsp) - datetime({fsp}).
  • char: NUMERIC_PRECISION = null; NUMERIC_SCALE = null; CHARACTER_MAXIMUM_LENGTH = 2 - 128; CHARACTER_OCTET_LENGTH = 2 - 384. CHAR(M) - char({m}).
  • varchar: NUMERIC_PRECISION = null; NUMERIC_SCALE = null; CHARACTER_MAXIMUM_LENGTH = 1 - 8192; CHARACTER_OCTET_LENGTH = 3 - 24576. VARCHAR(M) - varchar({m}).
  • blob/text: NUMERIC_PRECISION = null; NUMERIC_SCALE = null; CHARACTER_MAXIMUM_LENGTH = 65535; CHARACTER_OCTET_LENGTH = 65535; COLUMN_TYPE = blob/text (no (M)).

由上述统计得出 共需 4 个占位符: 精度 {p}、小数位 {s}、时间精度 {fsp}、最大字符数 {m}。

Resource - Official Manual

详见本文下方 "官方文档中 除上述 overview 之外 有用的文档" 小节。

Resource

  1. 《MySQL57从入门到精通_视频教学版_刘增杰_清华大学出版社_201609》 P104 5.2 如何选择数据类型
  1. MySQL 数据类型
  2. MySQL 支持的数据类型 (总结)
  3. MySQL 中有关 timestamp 和 datetime 的总结
  4. MySQL JSON 数据类型 操作
  1. MySQL 中的数据类型 enum 和 set
  2. MySQL 的 set 类型很简单
  3. MySQL set 类型字段的 SQL 操作知识介绍
  1. varbinary vs blob in MySQL - varbinary 被限定为: MySQL 5.0.2 及以前 255B (字节); MySQL 5.0.3 及以后 65KB (千字节)。BLOB 被限定为: 65KB。最终,二者实际上相同,除非想保持兼容于旧版本。

Resource - 日期和时间类型

  1. MySQL 中你应该使用什么数据类型表示时间
  2. MySQL 中时间存储类型的选择
  3. MySQL 日期用 datetime 好还是用 int10 好
  4. 用 char 储存时间与用 datetime 有什么区别

mysql> ? data types - 列出全部类型?

Note: 2018-03-04 研究发现该命令的返回 "并非 MySQL 的全部数据类型"、"并非全部都是数据类型关键字"。

《深入浅出MySQL数据库开发优化与管理维护_第2版_唐汉明_人民邮电出版社_201401 - Page 52、147 数据类型》

mysql> ? data types
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

MySQL 中 没有 nchar/nvarchar/ntext

截至 2017-12-11 Tony 的理解: MySQL 中 char/varchar/text 支持 utf8,因此不再需要专门的 nchar/nvarchar/ntext,因此有人说 char/varchar/text 与 nchar/nvarchar/ntext 是同义词。

官方文档

Standard SQL defines NCHAR or NATIONAL CHAR as a way to indicate that a CHAR column should use some predefined character set. MySQL uses utf8 as this predefined character set. For example, these data type declarations are equivalent:
CHAR(10) CHARACTER SET utf8
NATIONAL CHARACTER(10)
NCHAR(10)

As are these:
VARCHAR(10) CHARACTER SET utf8
NATIONAL VARCHAR(10)
NVARCHAR(10)
NCHAR VARCHAR(10)
NATIONAL CHARACTER VARYING(10)
NATIONAL CHAR VARYING(10)

You can use N'literal' (or n'literal') to create a string in the national character set. These statements are equivalent:
SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';

-- 10.3.7 The National Character Set
-- 9.1.1 String Literals

溢出

  • 当要在一个数值列内保存一个超出该列允许范围的值时,MySQL 的操作取决于此时有效的 SQL 模式。如果模式未设置,MySQL 将值裁剪到范围的相应端点,并保存裁减好的值。但是,如果模式设置为 traditional (严格模式),超出范围的值将被拒绝并提示错误,并且根据 SQL 标准插入会失败。
  • 如果 INT 列是 UNSIGNED,列范围的大小相同,但其端点会变为到 0 和 4294967295。如果你试图保存 -9999999999 和 9999999999,以非严格模式保存到列中的值是 0 和 4294967296。
  • 如果在浮点或定点列中分配的值超过指定 (或默认) 精度和标度规定的范围,MySQL 以非严格模式保存表示范围相应端点的值。
  • 当 MySQL 没有工作在严格模式时,对于 ALTER TABLE、LOAD DATA INFILE、UPDATE 和多行 INSERT 语句,由于裁剪发生的转换将报告为警告。当 MySQL 工作在严格模式时,这些语句将失败,并且部分或全部值不会插入或更改,取决于是否表为事务表和其它因素。

Display Width (显示宽度)

  1. 11.2.5 Numeric Type Attributes - MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
  2. Chapter 11 Data Types - M indicates the maximum display width for integer types.
  3. 11.1.1 Numeric Type Overview - M indicates the maximum display width for integer types. The maximum display width is 255. Display width is unrelated to the range of values a type can contain.
  • INT(M) 中的 (M) 叫做 "Display Width",用于指定 整型数值的显示宽度,当数值的位数 小于 指定的显示宽度时 在左侧填充空格。
  • 显示宽度 与 存储空间、取值范围 无关。不影响列内保存的值。不影响超过显示宽度的值的显示。
  • 默认的填充字符为空格。但是,当结合可选扩展属性 ZEROFILL 使用时,默认填充字符为 0。例如,对于声明为 INT(5) ZEROFILL 的列,数值 4 检索为 00004
  • 显示宽度的有效值上限为 255。
  • 定义列的数据类型时如未指定 显示宽度,则 MySQL 会自动指定默认显示宽度。例如 tinyint 取值范围为 -128-1270-255,由于负号占了一位,因此默认显示宽度为 4。详见以下测试:
mysql> create database if not exists tony_test;
Query OK, 1 row affected (0.00 sec)

mysql> create table tony_test.tb_0 (
    -> a0 tinyint unsigned,
    -> a1 tinyint,
    -> b0 smallint unsigned,
    -> b1 smallint,
    -> c0 mediumint unsigned,
    -> c1 mediumint,
    -> d0 int unsigned,
    -> d1 int,
    -> e0 bigint unsigned,
    -> e1 bigint
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc tony_test.tb_0;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| a0    | tinyint(3) unsigned   | YES  |     | NULL    |       |
| a1    | tinyint(4)            | YES  |     | NULL    |       |
| b0    | smallint(5) unsigned  | YES  |     | NULL    |       |
| b1    | smallint(6)           | YES  |     | NULL    |       |
| c0    | mediumint(8) unsigned | YES  |     | NULL    |       |
| c1    | mediumint(9)          | YES  |     | NULL    |       |
| d0    | int(10) unsigned      | YES  |     | NULL    |       |
| d1    | int(11)               | YES  |     | NULL    |       |
| e0    | bigint(20) unsigned   | YES  |     | NULL    |       |
| e1    | bigint(20)            | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

mysql> drop database if exists tony_test;
Query OK, 1 row affected (0.02 sec)

UNSIGNED

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.

When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled.

-- 11.1.1 Numeric Type Overview

  • 所有整数类型 可以有一个可选 (非标准) 属性 UNSIGNED。当你想要 在列内只允许非负数 和 该列需要较大的上限数值范围 时可以使用 无符号值。
  • 浮点和定点类型 也可以为 UNSIGNED。该属性防止负值保存到列中,但不同于整数的是 数值范围 保持不变。

BIT(M)

  1. MySQL bit 类型
  1. 9.1.5 Bit-Value Literals - Bit-value literals are written using b'val' or 0bval notation. val is a binary value written using zeros and ones. Lettercase of any leading b does not matter. A leading 0b is case sensitive and cannot be written as 0B. 要指定位值,可以使用 b'value' 标记法。value 是一个用 0 和 1 编写的二进制值,例如: b'111'b'100000000' 分别表示 7 和 128。
  2. 11.1.1 Numeric Type Overview - BIT[(M)]: A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted. 如果 M 被忽略 则 默认为 1。Tony 编码规范: 统一显式编码为 bit(1)
  3. 11.2.4 Bit-Value Type - BIT - The BIT data type is used to store bit values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64. If you assign a value to a BIT(M) column that is less than M bits long, the value is padded on the left with zeros. For example, assigning a value of b'101' to a BIT(6) column is, in effect, the same as assigning b'000101'. b'value' 标记法、LeftPad 特性: 如果为 bit(m) 列分配的值的长度小于 m 位,在值的左边用 0 填充。例如: 为 bit(6) 列分配一个值 b'101',其效果与分配 b'000101' 相同。BEWARE: MySqlDataReader.GetFieldType for BIT columns return UInt64 (sound like that is the type where multiple BIT fields are physically stored) for MySQL Connector/Net 6.9.8, MySQL 5.6.26. 2018-01-18 Tony 在编码中也遇到了此问题。
  4. 11.8 Data Type Storage Requirements - BIT(M) approximately (M+7)/8 bytes.
  • BIT(4) 能够存储的值为 0-15,因为 15 的二进制值为 1111
  • 查询 BIT 类型的数据时,可以使用 BIN(字段名+0) 来将值转换为二进制显示。

测试记录 (2018-01-19)

MySQL 中数据类型为 bit(1) 的列,查询时 DbDataReader["IsKey"] 的返回类型为 object {ulong}

唯二可行的强制转换是:

var a = (ulong)reader["IsKey"];
var a = (object)reader["IsKey"];

以下转换全部运行时报错:

var a = (long)reader["IsKey"];
var a = (uint)reader["IsKey"];
var a = (int)reader["IsKey"];
var a = (byte)reader["IsKey"];
var a = (bool)reader["IsKey"];

但通过索引进行各种 GetXxx(index) 转换均可行:

var a = reader.GetBoolean(5);
var a = reader.GetDouble(5);
var a = reader.GetInt16(5);
...

如无法得知索引,暂时采用以下方案获取 布尔值:

bool b = (ulong)reader["IsKey"] != 0;

测试记录 (2018-02-10)

INSERT 语句中,bit(1) 列的值可为 0 或者 1。不可为 '0''1'2,否则 MySQL 将报错 "Error Code: 1406. Data too long for column 'IsIncr' at row 1"。

MySQL 中存储 boolean/布尔值

网摘

  • MySQL 没有内置的布尔类型。但是它使用 tinyint(1)。为了更方便,MySQL 提供 booleanbool 作为 tinyint(1) 的同义词。
  • MySQL 将布尔值作为整数存储在 boolean/tinyint(1) 列中。inserttruefalse 分别被转换为 10 之后被写入。
  • 因为 boolean 类型是 tinyint(1) 的同义词,所以可以在布尔列中插入 10 以外的值,例如 2,注意此后 select 得到的也是 2
  • 如果要将结果输出为 truefalse,可以使用 if 函数,例如 select if(col0, 'true', 'false') from tb_name

Official

  1. 9.1.6 Boolean Literals - SELECT TRUE, true, FALSE, false; --1, 1, 0, 0
  2. 11.1.1 Numeric Type Overview - BOOL, BOOLEAN: These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true / 这些类型是 TINYINT(1) 的同义词。零值被认为是 false。非零值被认为是 true本文有专文小节摘录了该文档的大部分内容

stackoverflow.com

  1. http://stackoverflow.com/search?q=mysql+boolean
  2. Which MySQL data type to use for storing boolean values - 最新的答案是使用 bit(1)
  3. MYSQL boolean types
  4. MySQL Boolean Type
  5. Boolean vs tinyint(1) for boolean values in MySQL
  6. MySQL boolean - flipping the value? - update tb_name set bool_col = not bool_col where ... 布尔值取反

Other

  1. MySQL 中 boolean 类型
  2. MySQL 中 boolean 类型
  3. MySQL 数据库中 实现 true/false 的 数据类型 - ENUM, CHAR(1), CHAR(0), TINYINT(1)

数值类型

  • MySQL 中 浮点类型有两种: FLOATDOUBLE。定点类型只有一种: DECIMAL
  • 0-23 的精度对应 FLOAT 列的 4 字节单精度。24-53 的精度对应 DOUBLE 列的 8 字节双精度。
  • DECIMALNUMERIC 类型在 MySQL 中视为相同的类型。它们用于保存必须为确切精度的值,例如货币数据。Salary DECIMAL(5, 2) 中,5 是精度,2 是标度。精度表示保存值的主要位数,标度表示小数点后面可以保存的位数。
  • 在标准 SQL 中,语法 DECIMAL(M) 等价于 DECIMAL(M, 0)。同样,语法 DECIMAL 等价于 DECIMAL(M, 0),可以通过计算确定 M 的值。在MySQL 5.1 中支持 DECIMALNUMERIC 数据类型的变量形式。M 默认值是 10
  • DECIMALNUMERIC 的最大位数是 65

日期时间类型

  • 每个时间类型有 一个有效值范围 和 一个 "零" 值。当 MySQL 遇到一个日期或时间类型 超出范围 或 对于该类型不合法的值 时,它将该值转换为该类的 "零" 值。一个例外是超出范围的 time 值被裁剪到 time 范围的相应端点。"零" 值是特殊值,可以使用 '0000-00-00 00:00:00' 等形式显式保存或引用它们,也可以使用值 '0'0 来保存或引用,写起来更容易。
  • 如果值用于数值上下文中,MySQL 自动将日期或时间类型的值转换为数字,反之亦然。

timestamp[(m)]

timestamp 列用于 insertupdate 操作时记录日期和时间。如果你不分配一个值,表中的第一个 timestamp 列自动设置为最近操作的日期和时间。也可以通过分配一个 null 值,将 timestamp 列设置为当前的日期和时间。
timestamp 值返回后显示为 yyyy-mm-dd hh:mm:ss 格式的字符串,显示宽度固定为 19 个字符。如果想要获得数字值,应在 timestamp 列添加 +0

  • default current_timestamp 子句不用 on update 子句,列为默认值使用当前的时间戳但是不自动更新。
  • 不用 default 子句但用 on update current_timestamp 子句,列有默认值 0 并自动更新。

字符串类型

使用 UTF8 时为了节省空间,使用 varchar 而不要用 char。否则,MySQL 必须为一个 char(10) character set utf8 列预备 30 个字节,因为这是可能的最大长度。
-- 《MySQL_5.5中文参考手册.pdf》 10.5 Unicode 支持

  • char(255)varchar(65535) 已达到长度上限,超过 255/65535 建表时将报错。
  • varchar 保存时用一个字节或两个字节长的前缀+数据。如果 varchar 列声明的长度大于 255,长度前缀是两个字节。
  • 存储 身份证 用 char(18)
  • 存储 手机号码 用 char(11)
  • 存储 文章 用 text,无需给出可选长度。最大长度为 65,535(2^16–1) 字符。如果长度不够,下一档是 MEDIUMTEXT,最大长度为 16,777,215(2^24–1) 字符。

测试记录 - text 的 MaxLen

CREATE TABLE `code_gen`.`test0` (
  `Id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键。',
  `Name` text(21845) NOT NULL COMMENT '名称。',
  PRIMARY KEY (`Id`)
) COMMENT='test';

创建结果为:

CREATE TABLE `test0` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键。',
  `Name` text NOT NULL COMMENT '名称。',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test';
  • 将 21845 改为 21846 之后,创建结果由 text 变为 mediumtext。这说明默认即为 utf-8,导致 65535 / 3 = 21845。
  • 注意: 创建结果中 textmediumtext 后均无 (M)

测试记录 - blob & text

在 ``information_schema.columns`` 表中,
当 ``DATA_TYPE`` 列的值为 ``blob``          时,``COLUMN_TYPE`` 列的值为 ``blob``,         ``CHARACTER_MAXIMUM_LENGTH`` 与 ``CHARACTER_OCTET_LENGTH`` 列的值为 ``65535``。
当 ``DATA_TYPE`` 列的值为 ``text``          时,``COLUMN_TYPE`` 列的值为 ``text``,         ``CHARACTER_MAXIMUM_LENGTH`` 与 ``CHARACTER_OCTET_LENGTH`` 列的值为 ``65535``。
当 ``DATA_TYPE`` 列的值为 ``mediumblob``    时,``COLUMN_TYPE`` 列的值为 ``mediumblob``,   ``CHARACTER_MAXIMUM_LENGTH`` 与 ``CHARACTER_OCTET_LENGTH`` 列的值为 ``16777215``。
当 ``DATA_TYPE`` 列的值为 ``mediumtext``    时,``COLUMN_TYPE`` 列的值为 ``mediumtext``,   ``CHARACTER_MAXIMUM_LENGTH`` 与 ``CHARACTER_OCTET_LENGTH`` 列的值为 ``16777215``。
当 ``DATA_TYPE`` 列的值为 ``longblob``      时,``COLUMN_TYPE`` 列的值为 ``longblob``,     ``CHARACTER_MAXIMUM_LENGTH`` 与 ``CHARACTER_OCTET_LENGTH`` 列的值为 ``4294967295``。
当 ``DATA_TYPE`` 列的值为 ``longtext``      时,``COLUMN_TYPE`` 列的值为 ``longtext``,     ``CHARACTER_MAXIMUM_LENGTH`` 与 ``CHARACTER_OCTET_LENGTH`` 列的值为 ``4294967295``。
Note:
2^16 = 65535        +1
2^24 = 16777215     +1
2^32 = 4294967295   +1

测试记录 - varchar

在 ``information_schema.columns`` 表中,最长的 ``varchar`` 列,其 ``COLUMN_TYPE`` 列的值为 ``varchar(8192)``,8192*8=65536,``CHARACTER_MAXIMUM_LENGTH`` 列的值为 ``8192``,``CHARACTER_OCTET_LENGTH`` 列的值为 ``24576``,8192*3=24576。

测试记录 - varchar(65535)

在 MySQL Workbench 中执行:

CREATE TABLE `code_gen`.`tpl` (
  `Id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `Code` varchar(65535) NOT NULL COMMENT 'Razor 模板源码',
  `CodeMd5` char(32) NOT NULL COMMENT 'Razor 模板源码 的 MD5 值',
  `RelativeDir` varchar(300) NOT NULL COMMENT '输出路径中的 相对目录 部分',
  `Filename` varchar(30) NOT NULL COMMENT '输出路径中的 文件名 部分',
  `IsSeparated` bit(1) NOT NULL COMMENT '为数据库中的每个表 独立输出 还是 合并输出',
  `Remark` varchar(1000) NOT NULL COMMENT '备注',
  `IdOfTplGroup` int unsigned NOT NULL COMMENT '本模板所属模板组的键',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Razor 模板';

报错:

Error Code: 1074. Column length too big for column 'Code' (max = 21845); use BLOB or TEXT instead

Resource:
MySQL 建表时 超出行尺寸上限 - the maximum row size is 65535 bytes, which is shared among all columns. 65535 / 3 = 21845.

测试记录 - varchar(21845)

在 MySQL Workbench 中执行:

CREATE TABLE `code_gen`.`tpl` (
  `Id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `Code` varchar(21845) NOT NULL COMMENT 'Razor 模板源码',
  `CodeMd5` char(32) NOT NULL COMMENT 'Razor 模板源码 的 MD5 值',
  `RelativeDir` varchar(300) NOT NULL COMMENT '输出路径中的 相对目录 部分',
  `Filename` varchar(30) NOT NULL COMMENT '输出路径中的 文件名 部分',
  `IsSeparated` bit(1) NOT NULL COMMENT '为数据库中的每个表 独立输出 还是 合并输出',
  `Remark` varchar(1000) NOT NULL COMMENT '备注',
  `IdOfTplGroup` int unsigned NOT NULL COMMENT '本模板所属模板组的键',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Razor 模板';

报错:

Error Code: 1118. Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

字符串 - 单引号 or 双引号

A string is a sequence of bytes or characters, enclosed within either single quote (') or double quote (") characters.
字符串是一个字节或字符序列,包含在单引号(‘)或双引号(")字符中。

If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotation marks because a string quoted within double quotation marks is interpreted as an identifier.
如果启用了ANSI_QUERS SQL模式,则字符串文本只能在单引号中引用,因为双引号中引用的字符串被解释为标识符。

-- 9.1.1 String Literals

字符串连接 - MySQL 的一种特殊方式

Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:
相邻放置的引用字符串连接到一个

'a string'
'a' ' ' 'string'

-- 9.1.1 String Literals

官方文档的入口、MySQL 类据类型的分类、语法描述的约定 - 官方文档 《Chapter 11 Data Types》

本小节内容摘自 Chapter 11 Data Types,对应的汉语版为 "《MySQL_5.5中文参考手册.pdf》 第11章: 列类型"。

Table of Contents

  • 11.1 Data Type Overview
  • 11.2 Numeric Types
  • 11.3 Date and Time Types
  • 11.4 String Types
  • 11.5 Spatial Data Types (空间数据类型)
  • 11.6 The JSON Data Type
  • 11.7 Data Type Default Values
  • 11.8 Data Type Storage Requirements
  • 11.9 Choosing the Right Type for a Column
  • 11.10 Using Data Types from Other Database Engines

MySQL supports a number of SQL data types in several categories: numeric types, date and time types, string (character and byte) types, spatial types, and the JSON data type. This chapter provides an overview of these data types, a more detailed description of the properties of the types in each category, and a summary of the data type storage requirements. The initial overview is intentionally brief. The more detailed descriptions later in the chapter should be consulted for additional information about particular data types, such as the permissible formats in which you can specify values.
MySQL 支持 一些SQL数据类型 在几个种类: 数字类型、日期和时间类型、字符串 (字符和字节) 类型、空间类型、JSON 数据类型。

Data type descriptions use these conventions:

数据类型描述使用这些约定:

M indicates the maximum display width for integer types. For floating-point and fixed-point types, M is the total number of digits that can be stored (the precision). For string types, M is the maximum length. The maximum permissible value of M depends on the data type.
M 表示 整数类型的最大显示宽度。
对于 浮点类型 和 定点类型,M 是可以存储的位数 (精度)。
对于 字符串类型,M 是最大长度。
最大允许值取决于数据类型。

D applies to floating-point and fixed-point types and indicates the number of digits following the decimal point (the scale). The maximum possible value is 30, but should be no greater than M−2.
D 适用于 浮点类型 和 定点类型,并表示小数点之后的位数 (刻度)。
可能的最大值是 30,但不应大于 M-2。

fsp applies to the TIME, DATETIME, and TIMESTAMP types and represents fractional seconds precision; that is, the number of digits following the decimal point for fractional parts of seconds. The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)
fsp - fractional seconds precision, 小数秒的精度 适用于 TIMEDATETIMETIMESTAMP 类型,代表 小数秒的精度。即 小数点后的位数 代表秒的小数部分。
fsp 的值,如果有,必须在范围 0 到 6。
值为 0 表示没有小数部分。如果省略,默认精度为 0。(这与标准的 SQL 默认值为 6 不同,与以前的 MySQL 版本兼容)。

Square brackets ([ and ]) indicate optional parts of type definitions.

数值类型 - 官方文档 《11.1.1 Numeric Type Overview》

本小节内容摘自 11.1.1 Numeric Type Overview,对应的汉语版为 "《MySQL_5.5中文参考手册.pdf》 11.1.1. 数值类型概述"。

M indicates the maximum display width for integer types. The maximum display width is 255. Display width is unrelated to the range of values a type can contain, as described in Section 11.2, "Numeric Types". For floating-point and fixed-point types, M is the total number of digits that can be stored.
M 标示 整型 的 最大显示宽度。最大显示宽度 是 255。显示宽度 与 类型能包含的值的范围 无关。
对于 浮点类型 和 定点类型,M 是可以存储的位数总数。

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

BIT[(M)]
A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

The last two statements display the results shown because 2 is equal to neither 1 nor 0.

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This type is a synonym for INT.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
A packed "exact" fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.
UNSIGNED, if specified, disallows negative values.
All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
These types are synonyms for DECIMAL. The FIXED synonym is available for compatibility with other database systems.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
UNSIGNED, if specified, disallows negative values.
Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. See Section B.5.4.7, "Solving Problems with No Matching Rows".

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
UNSIGNED, if specified, disallows negative values.

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
These types are synonyms for DOUBLE. Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.

FLOAT(p) [UNSIGNED] [ZEROFILL]
A floating-point number. p represents the precision in bits, but MySQL uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT with no M or D values. If p is from 25 to 53, the data type becomes DOUBLE with no M or D values. The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.
FLOAT(p) syntax is provided for ODBC compatibility.

日期和时间类型 - 官方文档 《11.1.2 Date and Time Type Overview》

本小节内容摘自 11.1.2 Date and Time Type Overview,对应的汉语版为 "《MySQL_5.5中文参考手册.pdf》 11.1.2. 日期和时间类型概述"。

MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:
MySQL 允许 TIMEDATETIMETIMESTAMP 值 中的 小数秒,可达微秒精度 (6位数)。
定义一个 包括小数秒部分 的列,使用语法 type_name(FSP)

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

Any TIMESTAMP or DATETIME column in a table can have automatic initialization and updating properties.
表中的任何 DATETIMETIMESTAMP 列 可以拥有 自动 初始化 和 更新 的特性。

DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but permits assignment of values to DATE columns using either strings or numbers.

DATETIME[(fsp)]
A date and time combination. The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS[.fraction]' format, but permits assignment of values to DATETIME columns using either strings or numbers.
An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
Automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 11.3.5, "Automatic Initialization and Updating for TIMESTAMP and DATETIME".

TIMESTAMP[(fsp)]
A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the "zero" TIMESTAMP value.
An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
The way the server handles TIMESTAMP definitions depends on the value of the explicit_defaults_for_timestamp system variable (see Section 5.1.5, "Server System Variables").
If explicit_defaults_for_timestamp is enabled, there is no automatic assignment of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes to any TIMESTAMP column. They must be included explicitly in the column definition. Also, any TIMESTAMP not explicitly declared as NOT NULL permits NULL values.
If explicit_defaults_for_timestamp is disabled, the server handles TIMESTAMP as follows:
Unless specified otherwise, the first TIMESTAMP column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.
Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses. By default, the first TIMESTAMP column has these properties, as previously noted. However, any TIMESTAMP column in a table can be defined to have these properties.

TIME[(fsp)]
A time. The range is '-838:59:59.000000' to '838:59:59.000000'. MySQL displays TIME values in 'HH:MM:SS[.fraction]' format, but permits assignment of values to TIME columns using either strings or numbers.
An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

YEAR[(4)]
A year in four-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of values to YEAR columns using either strings or numbers. Values display as 1901 to 2155, and 0000.
Note: The YEAR(2) data type is deprecated and support for it is removed in MySQL 5.7.5. To convert YEAR(2) columns to YEAR(4), see Section 11.3.4, "YEAR(2) Limitations and Migrating to YEAR(4)".
For additional information about YEAR display format and interpretation of input values, see Section 11.3.3, "The YEAR Type".
The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

字符串类型 - 官方文档 《11.1.3 String Type Overview》

本小节内容摘自 11.1.3 String Type Overview,对应的汉语版为 "《MySQL_5.5中文参考手册.pdf》 11.1.3. 字符串类型概述"。

MySQL interprets length specifications in character column definitions in character units. This applies to CHAR, VARCHAR, and the TEXT types.
MySQL 以 字符单位 解释 字符列定义中的长度规范。这适用于 charvarchartext 类型。

The CHARACTER SET attribute specifies the character set, and the COLLATE attribute specifies a collation for the character set. For example:

CREATE TABLE t
(
    c1 VARCHAR(20) CHARACTER SET utf8,
    c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);

This table definition creates a column named c1 that has a character set of utf8 with the default collation for that character set, and a column named c2 that has a character set of latin1 and a case-sensitive collation.

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1.
M 代表列的长度,以字符计。范围 0-255。如省略 M 则长度为 1。

Trailing spaces are removed when CHAR values are retrieved unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.
char 值 被检索 时 尾随空格 被去除,除非 PAD_CHAR_TO_FULL_LENGTH SQL 模式 被启用。

CHAR is shorthand for CHARACTER. NATIONAL CHAR (or its equivalent short form, NCHAR) is the standard SQL way to define that a CHAR column should use some predefined character set. MySQL uses utf8 as this predefined character set.
VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the standard SQL way to define that a VARCHAR column should use some predefined character set. MySQL uses utf8 as this predefined character set.
MySQL 使用 utf8 作为 预定义的字符集。

MySQL permits you to create a column of type CHAR(0). This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value. CHAR(0) is also quite nice when you need a column that can take only two values: A column that is defined as CHAR(0) NULL occupies only one bit and can take only the values NULL and '' (the empty string).
MySQL 允许创建一个类型为 char(0) 的列。

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters.
M 代表列的最大长度,以字符计。范围 0-65,535。如忽略 M 则长度为 1。

MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
MySQL 存储 varchar 值 用 一个 1或2字节 的 前缀附加数据。这个长度前缀 标示 该值的字节数。一个 varchar 列,如果值不超过 255字节 则使用 1字节、如果值超过 255字节 则使用 2字节。

MySQL follows the standard SQL specification, and does not remove trailing spaces from VARCHAR values.
MySQL 遵循 标准的 SQL 规范,而 不去除尾随空格 从 varchar 值。

BINARY[(M)]
The BINARY type is similar to the CHAR type, but stores binary byte strings rather than nonbinary character strings. An optional length M represents the column length in bytes. If omitted, M defaults to 1.
binary 类型 类似于 char 类型,但 保存 二进制字节字符串 而不是 非二进制字符字符串。
可选的 M 表示 列的长度 (以 字节 为单位)。如省略 则 M 默认为 1。

VARBINARY(M)
The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the maximum column length in bytes.
varbinary 类型 类似于 varchar 类型,但 保存 二进制字节字符串 而不是 非二进制字符字符串。
M 表示 最大的列的长度 (以 字节 为单位)。

TINYBLOB
A BLOB column with a maximum length of 255 (2^8 − 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^8 − 1) characters. The effective maximum length is less if the value contains multibyte 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^16 − 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 BLOB type large enough to hold values M bytes long.
可以给出该类型的可选长度 M。如果给出,则 MySQL 将列创建为最小的但足以容纳 M 字节长的值的 blob 类型。

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
A TEXT column with a maximum length of 65,535 (2^16 − 1) characters. The effective maximum length is less if the value contains multibyte 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 TEXT type large enough to hold values M characters long.
可以给出该类型的可选长度 M。如果给出,则 MySQL 将列创建为最小的但足以容纳 M 字符长的值的 text 类型。

MEDIUMBLOB
A BLOB column with a maximum length of 16,777,215 (2^24 − 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^24 − 1) characters. The effective maximum length is less if the value contains multibyte 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^32 − 1) bytes. The effective maximum length of LONGBLOB 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^32 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.
如果该值包含多字节字符 则有效最大长度较小。
有效的最大的长度 取决于 客户端/服务器 协议中 配置的 最大包尺寸 和 可用的内存。

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. ENUM values are represented internally as integers.
只能有一个值的 字符串对象,该值选自 值列表、NULL、特殊的''错误值。enum 值在内部被表示为整数。
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group. For more information on these limits, see Section C.10.5, "Limits Imposed by .frm File Structure".
enum 列 最多可以有 65,535 个不同的元素。

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... SET values are represented internally as integers.
可以有零个或多个值 的 字符串对象,每个值必须来自值列表。
set 值在内部被表示为整数。
A SET column can have a maximum of 64 distinct members. A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group. For more information on this limit, see Section C.10.5, "Limits Imposed by .frm File Structure".
set 列 最多可以有 64 个不同的成员。

官方文档中 除上述 overview 之外 有用的文档

数值类型

  1. 11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT - 整型 (精确值) 清单、所占字节、取值范围
  2. 11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC - 定点类型 (精确值) DECIMAL 和 NUMERIC。precision 和 scale。MySQL 中 decimal 等价于 decimal(m) 继而等价于 decimal(m, 0)m 默认为 10decimal 最多 65 位。
  3. 11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE - 浮点类型 (近似值) FLOAT 和 DOUBLE、精度 与 小数 参数语法简介。
  4. 11.2.5 Numeric Type Attributes - 整型 的 显示宽度 (不影响存储、不影响超宽)。非标特性 ZEROFILL 导致 以 0 代替 LeftPad 中的空格。非标特性 UNSIGNED 浮点/定点 与整型不同 不移动端点。指定 ZEROFILL 会自动加上 UNSIGNED。关于 AUTO_INCREMENT 赋值。
  5. 11.2.6 Out-of-Range and Overflow Handling - 超出范围 和 溢出 处理

日期和时间类型

  1. 11.3 Date and Time Types - 每个时间类型都有一组有效值范围,以及一个 zero 值,当指定一个 MySQL 无法表示的 无效值时 可以使用该值。日期部分 必须总是 年-月-日 次序、而不是别处常用的 月-日-年日-月-年 次序。2位数的年份。日期时间值 与 数值 会自动转换。超出范围 或 无效 将转换为 zero 值。更多关于 zero 值。
  2. 11.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME - 自动初始化和更新 时间戳 和 日期时间
  3. 11.3.6 Fractional Seconds in Time Values - fsp (fractional seconds part, 秒的小数部分)
  4. 11.3.7 Conversion Between Date and Time Types - 时间类型之间的转换

字符串类型

  1. 9.1.1 String Literals
  2. 11.4.1 The CHAR and VARCHAR Types - char(30) 是 30 个字符而非字节。char 检索时会去除 RightPad 空格。char 0-255、varchar 0-65,535。65,535 字节须与行中所有列共享。varchar 存储了 1或2字节的 前缀附加数据 用于表示值的字节数。非严格模式下 分配超长值 将截断并警告、严格模式下 截断非空字符 将阻止插入并报错。不管哪种 SQL 模式: 对于 varchar 列,截断超长的尾随空格 优先于 插入和警告; 对于 char 列,截断超长的尾随空格 是静默执行。varchar 保存时不填充、存取时尾随空格被保持,以符合标准 SQL。注意: 有尾随空格的值 存入 char,取出时已无尾随空格。不管哪种 SQL 模式、哪个 MySQL 版本: 所有 字符字符串 比较时 无关 尾随空格 (含 a=b、不含 like)。a 与 ``a `` 会导致 "duplicate-key" 错误。
  3. 11.4.2 The BINARY and VARBINARY Types - 类似于 charvarchar,但 前者包含字节、后者包含字符,前者的长度以字节计、后者以字符计。非严格模式下 分配超长值 将截断并警告、严格模式下 截断非空字符 将阻止插入并报错。binary(m) 插入时 RightPad 0x00、查询时不去除。RightPad 部分参与比较 (包括 ORDER BY 和 DISTINCT 操作),0x00 < space。但是 aa\0 会导致 "duplicate-key" 错误。如果 取回的值 必须与 存入的未填充的值 相同,则最好使用 varbinaryblobvarbinary 插入无填充、查询无去除。
  4. 11.4.3 The BLOB and TEXT Types - BLOB (binary large object, 二进制大对象) 可以容纳可变数量的数据。blob 存储字节、text 存储字符。非严格模式下 分配超长值 将截断并警告、严格模式下 截断非空字符 将阻止插入并报错。不管哪种 SQL 模式: 对于 text 列,截断超长的尾随空格 总是警告。blobtext 插入无填充、查询无去除。如果 text 列被索引、则索引条目比较将在末尾填充空格,且 aa `` 会导致 "duplicate-key" 错误,而 ``blob 不这样。blobtext 不能有默认值。还有很多未翻译,详见中文文档
  5. 11.4.4 The ENUM Type - 2017-12-14 未细看、未翻译
  6. 11.4.5 The SET Type - 2017-12-14 未细看、未翻译

存储需求 - 各数据类型 所占字节

文档: 11.8 Data Type Storage Requirements - 汉语版: 《MySQL_5.5中文参考手册.pdf》 11.5. 列类型存储需求

decimal 的 精度 与 小数位

相关文档
  • 官方文档摘录: Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table (使用二进制格式将 9 个十进制 (基于 10) 数压缩为 4 个字节来表示 DECIMAL 列值。每个值的整数和分数部分的存储分别确定。每个 9 位数的倍数需要 4 个字节,并且 "剩余的" 位需要 4 个字节的一部分。下表给出了超出位数的存储需求:).
  • MySQL decimal(m,d) 的说明
  • MySQL 数据类型 DECIMAL 用法
TonyNote

因为 "整数和分数部分的存储分别确定",所以 decimal(18,9) 用途广泛且空间利用率高。

其它

  1. 11.7 Data Type Default Values - 数据类型的默认值。2017-12-14 未细看、未翻译
  2. 10.5.4 Microsoft SQL Server Type Mapping
  1. 《MySQL_5.5中文参考手册.pdf》 11. 列类型
  2. 《MySQL_5.5中文参考手册.pdf》 9.1. 文字值
  3. 《MySQL_5.5中文参考手册.pdf》 24.2. decimal 数据类型更改