mysql数据类型+运算符

数据类型

一、数据类型简介

数据表由多列字段构成，每一个字段指定了不同的数据类型，指定了数据类型之后，也就决定了向字段插入的数据内容;
不同的数据类型也决定了 MySQL 在存储它们的时候使用的方式，以及在使用它们的时候选择什么运算符号进行运算;
数值数据类型：TINYINT 、SMALINT 、MEDIUMINT 、INT 、BIGINT 、FLOAT 、DOUBLE 、DECIMAL；
日期/时间类型：YEAR 、TIME 、DATE 、DATETIME 、TIMESTAMP；
字符串类型：CHAR 、VARCHAR 、BINARY 、VARBINARY 、BLOB 、TEXT 、ENUM 、SET。

二、数值类型简介

数值类型主要用来存储数字，不同的数值类型提供不同的取值范围，可以存储的值范围越大，所需要的存储空间也越大；
数值类型分为：①整数类型 ②浮点数类型 ③定点数类型。

1、整数类型如下：

示例：

mysql> create database zjz;  #创建zjz库
Query OK, 1 row affected (0.30 sec)
mysql> use zjz;  #进入zjz库
Database changed
mysql>  create table t1(
    -> z tinyint,
    -> h smallint,
    -> a mediumint,
    -> n int,
    -> g bigint unsigned    #默认是有符号的列，unsigned表示无符号列
    ->  );
Query OK, 0 rows affected (0.08 sec)

查看表的详细信息如下（在创建表的时候没有指定其长度，但是每一列都有自己默认的长度）：

2、浮点数类型和定点数类型

MySQL 中使用浮点数和定点数来表示小数，浮点数有两种类型：单精度浮点数（FLOAT）和双精度浮点数（DOUBLE），定点数只有 DECIMAL；
浮点数和定点数都可以用 (M，N) 来表示，其中 M 是精度，表示总共的位数，N 是标度，表示小数的位数，如：3.145，用M/N来表示就是4，3；
DECIMAL 实际是以字符串形式存放的，在对精度要求比较高的时候（如货币、科学数据等）使用 DECIMAL 类型会比较好；
浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围，它的缺点是会引起精度问题。

优化建议：

建议使用 TINYINT 代替 ENUM、BITENUM、SET；
避免使用整数的显示宽度，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT；
DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置；
  建议使用整型来运算和存储实数，方法是，实数乘以相应的倍数后再操作；
  整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

示例1：

新建一个表，值的长度都为5，小数点后都是两位
mysql> create table t2(
    -> z float(5,2),
    -> h double(5,2),
    -> a decimal(5,2)
    -> );
Query OK, 0 rows affected (0.12 sec)
插入正常符合要求的数据，可以正常插入
mysql> insert into t2 values(123.45,123.45,123.45);
Query OK, 1 row affected (0.00 sec)
插入一些不符合规定的数据，会返回1个warning信息
mysql> insert into t2 values(123.456,123.456,123.456);
Query OK, 1 row affected, 1 warning (0.01 sec)
查看warning信息，提示a列有截断的数据
mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'a' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

通过实际插入的数据不难发现，如果插入不符合列规定的数据，那么最终会以四舍五入的方法处理。

需要注意的是，在上面的数值类型中，它只允许在小数点后面多一位，而不允许在小数点之前多一位，如插入1234.5或1234.35就会报错。

示例2：

创建多个列，长度都为10，小数点后面有两位
mysql> create table t3( 
    -> z float(10,2),
    -> h double(10,2),
    -> a decimal(10,2)
    -> );
mysql> insert into t3 values(12345678.123,12345678.123,12345678.123);
Query OK, 1 row affected, 1 warning (0.00 sec)
查看warning信息，提示a列有截断的数据
mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'a' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

上述示例插入的数据，实际显示如下：

在上面的表中，z列为float数值类型，其他两列的数值还是基于四舍五入的方法进行插入的，但是float数值类型的z列，插入的数据和实际输入的数据就有些出入了，并且会随着小数点位数的增加，这个浮动范围会更大。

3、日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

优化建议：

MySQL能存储的最小时间粒度为秒。
建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd；
用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串；
当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后）， MySQL会自动返回记录插入的确切时间；
TIMESTAMP是UTC时间戳，与时区相关；
DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关；
除非有特殊需求，否则建议使用TIMESTAMP，它比DATETIME更节约空间。

1）YEAR

格式1：以4位字符串格式表示的 YEAR ，范围为 '1901' ~ '2155'；
格式2：以4位数字格式表示的 YEAR ，范围为 1901 ~ 2155；
格式3：以2位字符串格式表示的 YEAR ，范围为 '00' ~ '99' ，其中，'00' ~ '69' 被转换为 2000 ~ 2069 ，'70' ~ '99' 被转换为