MYSQL 的数据类型

 1.1整数类型

TINYINT:1字节  -128--127

SMALLINT: 2字节  -32768--32767

MEDIUMINT:3字节 -8388608--8388607

INT：4字节-21473648--21473647

BIGINT：8字节-9223372036854775808—9223372036854775807

mysql> create table t1(a tinyint, bsmallint,c int(5)) ;

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert  into t1 values(127,32767,123456);

Query OK, 1 row affected (0.01 sec)

 

mysql> select * From t1;

+------+-------+--------+

| a   | b     | c      |

+------+-------+--------+

| 127 | 32767 | 123456 |

+------+-------+--------+

1 row in set (0.00 sec)

 

mysql> insert  into t1 values(128,32768,1234567890);

Query OK, 1 row affected, 2 warnings (0.00sec)

 

mysql> show warnings;

+---------+------+--------------------------------------------+

| Level  | Code | Message                                    |

+---------+------+--------------------------------------------+

| Warning | 1264 | Out of range value forcolumn 'a' at row 1 |

| Warning | 1264 | Out of range value forcolumn 'b' at row 1 |

+---------+------+--------------------------------------------+

2 rows in set (0.00 sec)

 

mysql> select * from t1;

+------+-------+------------+

| a   | b     | c          |

+------+-------+------------+

| 127 | 32767 |     123456 |

| 127 | 32767 | 1234567890 |

+------+-------+------------+

2 rows in set (0.00 sec)

 

另外整数类型有个特性：就是auto_increment

一个表中只能有一个auto_increment列，对于任何想要使用auto_increment的列都应该定义为not null，并且设置为primary key，或者定义为unique

 

Create table t2（id auto_increment not null，primary key（id））

1.2、浮点类型和定点类型

FLOAT:4字节 

DOUBLE:8字节

Dec（M,D） :M+2字节

DECIMAL(M,D)：M+2字节

 

对于小数的表示mysql 可以分为定点和浮点，浮点包括单精度和双精度，而定点只有decimal

浮点和定点都可以用（M,D）的方式来表示，M表示该值一共显示M位数字（整数+小数），D表示小数点后面几位

 

Float和double 在不指定精度的时候默认会按照实际精度来显示

Decimal 在不指定精度时，默认的整数位是10，默认的小数位是0，也就是decimal（10,0）

 

mysql> create table t2(a float(5,2),bdouble(5,2),c decimal(5,2));

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into t2values(1.236,1.236,1.236);

Query OK, 1 row affected, 1 warning (0.00sec)

 

mysql> select * From t2;

+------+------+------+

| a   | b    | c    |

+------+------+------+

| 1.24 | 1.24 | 1.24 |

+------+------+------+

1 row in set (0.00 sec)

 

我们可以发现，小数位后面被四舍五入并且截断了，

 

1.3、日期时间类型

YEAR:1字节 1901-2155

DATE:4字节 1000-01-01至9999-12-31

TIME:3字节：-838:59:59至838:59:59

DATATIME:8字节 1000-01-0100:00:00 至9999-12-31 23:59:59

mysql> create table t2(a datetime,bdate,c timestamp,d time,e year);

Query OK, 0 rows affected (0.09 sec)

 

mysql> insert into t2 values(now(),now(),now(),now(),now());

Query OK, 1 row affected, 2 warnings (0.00sec)

 

mysql> select *  from t2;

+---------------------+------------+---------------------+----------+------+

| a                   | b          | c                   | d        | e   |

+---------------------+------------+---------------------+----------+------+

| 2015-04-08 08:22:52 | 2015-04-08 |2015-04-08 08:22:52 | 08:22:52 | 2015 |

+---------------------+------------+---------------------+----------+------+

1 row in set (0.00 sec)

 

从上面你可以看出每个日期类型的不同点，去选择自己适合的数据类型。

还有一个日期类型timestamp，他和datetime的数据格式一样，但是还是有点小小的区别：

 

1、Timestamp一般会给表中第一个timestamp列设置的默认值为系统日期current_timestamp

2、Timestamp 在插入日期时候会先转换为本地时区后存放

3、timestamp 只能存放2038年的某一天，不适合存放比较久远的日期，而datetime 的范围是1000-01-01 00:00:00 9999-12-31 23:59:59

4、timestamp 受到mysql 版本和服务器的影响比较大

 

1.4、字符串类型

CHAR:CHAR类型长度设定后为固定值

VARCHAR:VARCHAR类型长度设定后可变

ENUM：枚举类型

SET：枚举类型

ENUM类型只能在取值列表内取一个值，SET类型可以在取值列表内取多个值

 

Char 和varchar的主要区别是：存储方式不同，char长度固定，为创建表时的长度，而varchar 长度不固定，char 删除尾部空格，varchar则保留尾部空格

 

mysql> create table t2(a char(4),bvarchar(4));

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into t2 values('ab ','ab') ;

Query OK, 1 row affected (0.00 sec)

 

mysql> select length(a),length(b) fromt2;

+-----------+-----------+

| length(a) | length(b) |

+-----------+-----------+

|        2 |         3 |

+-----------+-----------+

1 row in set (0.00 sec)

 

mysql> selectconcat(a,'+'),concat(b,'+') from t2;

+---------------+---------------+

| concat(a,'+') | concat(b,'+') |

+---------------+---------------+

| ab+           | ab +          |

+---------------+---------------+

1 row in set (0.00 sec)

 

枚举类型：enum对于1-255个成员的枚举需要1个字节存放，对于255-65535个成员需要2个字节存放，最多存放655535个成员。

mysql> create table t2(genderenum('m','f'));

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into  T2values('m'),('f'),('m'),('1'),(NULL);

ERROR 1146 (42S02): Table 'test.T2' doesn'texist

 

---此错误说明：mysql 标明的大小写是区分的，注意表名大小写

 

mysql> insert into  t2values('m'),('f'),('m'),('1'),(NULL);

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0  Warnings: 0

 

mysql> insert into t2 values('F');

Query OK, 1 row affected (0.00 sec)

 

Enum 插入的是大写字符，然后我们查看插入效果，发现是小写，说明，enum不区分大小写，主要取决于enum定义时的字符

mysql> select * From t2;

+--------+

| gender |

+--------+

| m     |

| f     |

| m     |

| m     |

| NULL  |

| f     |

+--------+

6 rows in set (0.00 sec)

 

 

Set 类型里面可以包含0-64个成员，和enum的主要区别是set类型可以一次选取多个成员，enum则只能选择一个。

 

mysql> create table t2(aset('a','b','c','d','e'));

Query OK, 0 rows affected (0.06 sec)

 

mysql> insert into t2values('a,b'),('d,e');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0  Warnings: 0

 

mysql> select * From t2;

+------+

| a   |

+------+

| a,b |

| d,e |

+------+

2 rows in set (0.00 sec)

1.5、二进制类型

BINATY(M)

VARBINARY(M)

BIT(M)

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB