MySQL Data Types

Character Data

  • be stored as either fixed-length or variable-length strings;
  • fixed-length
    • right-padded with spaces and always consume the same number of bytes,
  • variable-length
    • not right-padded with space and don't always consume the same number of bytes.
  • Defination
    • char(20) /* fixed-length, store string up to 20 characters in length */
    • varchar(20) /* variable-length */

注: char 不同于 text，char最长为255bytes，当需要使用长字符串时，请使用text.

Characters sets

  • SHOW CHARACTER SET; 显示字符编码类型和长度
  • varchar(20) character set $char_type; 设置特定某个field的字符类型
  • create DATABASE $DATABASE_NAME character set $char_type; 设置整个数据库的编码类型

Text data

Text type 最大字节数
tinytext 255
text 65,535
mediumtext 17,777,215
longtext 4,294,967,295
  • 当数据超过最大字节数，将会被删节
  • 当数据导入时，结尾空格符不会被删除
  • 当使用text类进行排序和分组时，只会使用开始的1024个bytes
  • 此类型为MySQL所特有
  • 如今varchar可达到65,535字节，因此，并不会专门使用tinytext和text

Numeric Data

INT

Type Signed range Unsigned range
tinyint -128 to 127 0 to 255
smallint -32,768 to 32,767 0 to 65,535
mediumint -8,388,608 to 8,388,607 0 to 16,777,215
int -2,147,483,648 to 2,147,483,647 0 to 4,294,967,295
bgint -2^63 to 2^63-1 0 to 2^64-1

FLOAT

Type Numeric range
float(p,s) −3.402823466E+38 to −1.175494351E-38
and 1.175494351E-38 to 3.402823466E+38
double(p,s) −1.7976931348623157E+308 to −2.2250738585072014E-308
and 2.2250738585072014E-308 to 1.7976931348623157E+308

  • p: precision the total number of allowable digits both to the left and to the right of the decimal point.

  • s: scale the number of allowable digits to the right of the demical point.

  • example

    • float(4,2)可以很好的存储27.44和8.19；但对于17.8675，会存为17.87(四舍五入)，对于178.375则会报错

Temporal DATA

Type Default format Allowable values
date YYYY-MM-DD 1000-01-01 to 9999-12-31
datetime YYYY-MM-DD HH:MI:SS 1000-01-01 00:00:00.000000
to 9999-12-31 23:59:59.999999
timestamp YYYY-MM-DD HH:MI:SS 1970-01-01 00:00:00.000000
to 2038-01-18 22:14:07.999999
year YYYY 1901 to 2155
time HHH:MI:SS −838:59:59.000000
to 838:59:59.000000

  • datetime, time, timestamp可以使用(num)限定秒的有效位数，如datetime(2)限制到百分秒级别。

  • timestamp 自动添加时间

results matching ""

    No results matching ""