【mysql数据库系列】mysql数据类型以及navicate中宽度设置值

MYSQL支持常用的数据类型：数值类型、日期/时间类型和字符串（字符）类型。

1.数值类型

 数值类型可以分为两类：整型和实数。对于实数，MYSQL支持确切精度的值（定点数）和近似精度的值（浮点数）。确切精度的数值类型有DECIMAL类型，近似精度的数值类型有单精度（FLOAT）或双精度（DOUBLE）两种类型。

      （1）整型

         整型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 下表展示了各种类型的空间占用以及表示的数值范围。

          

类型	字节	最小值（带符号/无符号）	最大值（带符号/无符号）
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551616

MYSQL 整型可设置一个"width"属性，这个点很容易让人混淆。实际上这不是一个精度，只是告诉客户端工具显示多少个字符而已，如INT（11）：11表示的不是数值范围，只是显示宽度（告诉交互式工具显示宽度，如Mysql客户端）。

上图的 11代表的是告诉客户端显示的宽度。由于MYSQL的内部类型只支持到秒级的精度，因此可以用BIGINT来存储精度到毫秒的时间戳。

（2）DECIMAL和NUMRIC(定点数)

定点数也就是DECIMAL型，指的是数据的小数点的位置是固定不变的。也就是说，小数点后面的位数是固定的。

DECIMAL 和NUMRIC在MYSQL中被视为相同的类型。它们用于保存必须为确切精度的值，例如货币数据。当声明该类型的列的时候，可以（并且通常要）指定精度和指标；比如 ，在DECIMAL(M,D )中，M是精度，表示数据的总长度，也就是十进制数的位数，不包括小数点；D是标度，表示小数点后面的数字位数。MYSQL5.1中，M的范围是1-65，D的范围是0到30且不能大于M。例如

price DECIMAL(5,2) 5代表精度，2代表标度，即 总长度为5，小数为2位。

在MYSQL5.1中以二进制格式保存DECIMAL和NUMRIC的值。如果值太大超出了BIGINT的范围，可以用DECIMAL来存储整型。

定点数表达发的缺点在于其形式过于死板，固定的小数点位置决定了固定位数的整数部分和小数部分，不利于同时表达特别大的数或者特别小的数。

（3）FLOAT和DOUBLE类型（浮点数）

浮点数（floating-point number ）是属于有理数中某个特定子集的数表示法，在计算机中用于近似的表示任意某个实数。具体来说，这个实数是由一个整数或定点数（即位数）乘以某个基数（计算机中通常是2）的整数次幂（指数）得到的。

在MYSQL中，对于浮点列类型，单精度（FlOAT）使用4个字节，双精度值（DOUBLE）使用8个字节。浮点数可以比整型、定点数表示更大的数值范围。

为了保证最大可能的移植性，对于使用近似数值存储的代码，应使用FLOAT或DOUBLE来表示，不规定精度或位数。由于浮点数存在误差问题，如果用到浮点数，

要特别注意误差问题，并尽量避免做浮点数比较。

MYSQL允许使用非标准语法：FLAOT(M,D)或者DOUBLE(M,D).这里，“(M,D)”表示该值一共显示了M位整数，其中D位整数位于小数点后面。例如，定义为FLOAT(7,4)

-999.9999。MYSQL保存值时会进行四舍五入，因此如果在FLOAT(7,4)列内插入999.000009，近似结果是999.0001。

浮点型（FLOAT/DOUBLE）对比定点类型（DECIMAL）使用的空间更少，所以为了减少存储空间，应尽量不要使用DECIMAL，除非是在保存确切精度的值时候，比如钱相关的。

（4）日期/时间类型

表示时间值的日期和时间类型有DATETIME、DATE、TIMESTAMP、TIME、和YEAR。每个事件类型都有一个有效的值范围，TIMESTAMP类型有其特有的自动更新的功能。

如果试图插入一个不合法的日期，MYSQL将给出警告或错误。如果没有使用NO_ZERO_DATE的SQL模式，默认情况下，MYSQL只允许在DATE或DATETIME列保存月和日是零的日期。这在应用程序中需要保存一个你不知道的确切日期的生日时非常有用，在这种情况下，只需要将日期保存为'1999-00-00'

如果不使用NO_ZERO_DATE SQL 模式，MYSQL还允许将 0000-00-00保存为伪日期。这在某些情况下比使用NULL值更方便，并且数据和索引占用的空间更小。

MYSQL以标准输出格式检索给定日期或时间类型的值，但它会尽力解释你指定的各种输值格式。尽管MSYQL在尝试几种格式的值时候，日期总是以“年-月-日”的顺序（例如：98-09-08）来处理的，而不是以 “月-日- 年” 或 “日- 月- 年”的顺序（例如： “09-04-98”、“04-09-08”）。

包含两位年值的日期会让人摸不着头脑，因为不知道世纪。MSYQL使用以下规则解释两位年值的日期。

70-99范围的年值均转化为1970-1999

00-69 范围的年值 均转换为 2000- 2069

  所以说一般数据库定义的时候尽量的按照严格的数据类处理，否则后续可能出现莫名其妙的麻烦。

（1）DATETIME 、DATE、TIMESTAMP类型。

当需要同时包含日期和时间信息的值的时候，建议用DATETIME(日期时间组合)类型。MSYQL以'YYYY-MM-DD HH:MM:SS' 的格式检索和显示DATETIME的值，但是允许使用字符串或数字为DATETIME列分配值。支持的范围为“1000-01-01 00：00：00”到“9999-12-3123：59：59”。DATETIME类型占8个字节。

当只需要日期值而不需要时间部分时，建议使用DATE（日期）类型。

MYSQL使用'YYYY-MM-DD'格式检索和显示DATE值，但是允许使用字符串或数字为DATE列分配值。支持的范围是'1000-01-01'到'9999-12-31'.DATE类型占3个字节。

TIMESTAMP（时间戳）列用于在进行INSERT或者UPDATE操作时候记录日期和时间。TIMESTAMP列的显示格式与DATETIME列相同。换句话说，显示宽度固定在19个字符，并且格式为'YYYY-MM-DD HH:MM:SS'。TIMESTAMP的范围从'1970-01-01 00:00:01' UTC 到'2038-01-0903:14-07'UTC。TIMESTAMP类型占4个字节。

TIMESTAMP的值以UTC格式进行保存的，存储的时候会对当前的时区进行转换，检索时候再转换会当前时区。当前时区对应的是time_zong系统变量。

控制TIMESTAMP列的初始化和更新的规则如下。

如果将TIMESTAMP类型字段定义为default current_timestamp ，那么插入一条记录的时候，该TIMESTAMP字段自动被赋值为当前时间。

可以将这些类型联合使用，如default current_timestam on update current_timestamp.

  可以给TIMESTAMP字段指定一个默认值，也可以在SQL语句中指定TIMESTAMP字段的值。

   可以使用任何常见格式指定DATETIME、DATE和TIMESTAMP的值。

对于'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'格式的字符串，允许 “不严格”语法：任何标点符号都可以用做日期部分或时间部分之间的分隔符。

 （2）TIME（时间类型）

该时间类型的范围是'-838:59:59'到 '838:59:59'。MYSQL以'HH:MM:SS'格式检索和显示TIME值（或者对于大的小时采用'HHH:MM:SS'格式）。注意标红的没有写错呢

。但是允许使用字符串或数字为TIME列分配值。TIME类型占用3个字节。

就足够让我颠覆了，在我的印象里貌似它一直是小于24的值，现在看起来一直都是错的，Time类型不仅可以用于表示一天的时间（必须小于24小时）,还可能为某个过去的时间或两个事件的时间间隔（可以大于24小时，或者未负）

为TIME列分配简写值时应注意。没有冒号，MySQL解释值时假定最右边的两位表示秒。(MySQL解释TIME值为过去的时间而不是当天的时间）。例如，你可能认为'1112'和1112表示'11:12:00'(11点过12分)，但MySQL将它们解释为'00:11:12'(11分，12 秒)。同样，'12'和12被解释为 '00:00:12'。相反，TIME值中使用冒号则肯定被看作当天的时间。也就是说，'11:12'表示'11:12:00'，而不是'00:11:12'。

超出TIME范围但合法的值被裁为范围最接近的端点。例如，'-850:00:00'和'850:00:00'被转换为'-838:59:59'和'838:59:59'。

无效TIME值被转换为'00:00:00'。请注意由于'00:00:00'本身是一个合法TIME值，只从表内保存的一个'00:00:00'值还不能说出原来的值是 '00:00:00'还是不合法的值。

   从这里找过来的  https://www.cnblogs.com/zhangjun516/archive/2013/03/14/2959489.html。就不去找官方文档了。

（3）YEAR类型

YEAR类型占用一个字节。

YEAR类型表示两位或者四位格式的年。MYSQL以YYYY格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值。

默认是四位格式，在四位格式中，允许的值是1901-2155 和0000

在两位格式中，如果两位是字符串，那么范围为'00-99'。和'70-99'范围的值分别被转换为2000-2069 和1970 -1999范围的YEAR值。

如果是两位整数，范围为1-99. 1-69 和70-99范围的值被分别转换为2001-2069 和1970 -1999范围的YEAR值。请注意，两位整数范围与两位字符串范围稍微有所不同。因为你不能直接将零指定为数字并且解释为2000.你必须将它指定为一个字符串'0'或者'00',或者它被解释为0000.

4 字符串类型

 字符串类型指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

 4.1 CHAR和VARCHAR类型

   CHAR与VARCHAR类型相似，但是它们保存和检索数据的方式不同。

CHAR和VARCHR类型声明的长度表示你想要保存的最大字符数。例如CHAR（30）可以占用30个字符。注意，在CHAR（M）、VARCHAR（M）中声明里M代表的是字符数，而不是字节数。

如果分配给CHAR或VARCHAR列的值超过了列的最大长度，则对值进行裁剪以使其长度适合。如果被裁剪掉的字符串不是空格，则会产生一条警告。

CHAR是固定长度的字符串，它的长度固定为创建表时候声明的长度。长度范围为0到255个字符。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格会被删掉，这是MYSQL服务器基本控制，和存储引擎无关。CHAR类型时候存储大部分值的长度都差不多的数据，例如MD5值。

VARCHR列中的值为可变长度的字符串。长度可以指定为0到65535之间的值（VARCHAR的最大有效长度由最大记录长度和使用的字符集确定。整体最大长度是65532字节。）相对于固定长度的字符串，它需要更少的存储空间。在保存VARCHAR的值时候，指保存需要的字符数，然后用1-2个字节来存储值的长度，所以如果是很短的值（如仅有1个字符），那么耗费的存储空间比CHAR还会多一些，所以如果想存储很短的类型，使用CHAR会更合适。VARCHAR可选的一种常见是最长记录的长度值比平均长度的最大得多。

保存VARCHAR的值的时候不会进行填充。当值保存和检索时尾部的空格仍然会保留，这一点符号标准SQL。