(d) MySQL data type

Data Type Basic Introduction

• Numeric type

  整形类型：tinyint,int,bigint浮点类型：float,double

• String type

  char系列：char varchartext系列：textblob系列：blob枚举类型：ENUM集合类型：SET

• Time Date Type

  date time datetime  timestamp year

• tinyint and int plastic testing

  mysql> create table test1(tinyint_test tinyint,int_test int);Query OK, 0 rows affected (0.03 sec)

  mysql> desc test1;+--------------+------------+------+-----+---------+-------+| Field        | Type       | Null | Key | Default | Extra |+--------------+------------+------+-----+---------+-------+| tinyint_test | tinyint(4) | YES  |     | NULL    |       || int_test     | int(11)    | YES  |     | NULL    |       |+--------------+------------+------+-----+---------+-------+2 rows in set (0.05 sec)

  mysql> insert into test1 values(111,111);Query OK, 1 row affected (0.01 sec)

  mysql> select * from test1;+--------------+----------+| tinyint_test | int_test |+--------------+----------+|          111 |      111 |+--------------+----------+1 row in set (0.00 sec)

  mysql> insert into test1(tinyint_test) values(128);                                                                                                                                ERROR 1264 (22003): Out of range value for column ‘tinyint_test‘ at row 1

  mysql> insert into test1(int_test) values(mysql> insert into test1(int_test) values(2147483647);Query OK, 1 row affected (0.01 sec)

  mysql> insert into test1(int_test) values(2147483648);ERROR 1264 (22003): Out of range value for column ‘int_test‘ at row 1

• Shaping width Test

  mysql> create table test2(id1 int,id2 int(8));

  mysql> desc test2;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id1   | int(11) | YES  |     | NULL    |       || id2   | int(8)  | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+

  mysql> insert into test2 values(1,1);

  mysql> select * from test2;+------+------+| id1  | id2  |+------+------+|    1 |    1 |+------+------+

  mysql> create table test3(id1 int zerofill,id2 int(8) zerofill);

  mysql> insert into test3 values(123456789,123456789);

  mysql> select * from test3;+------------+-----------+| id1        | id2       |+------------+-----------+| 0123456789 | 123456789 |+------------+-----------+

  Summary: The shaping width is always done with padding, with no practical meaning

• Floating-point type test-float

  mysql> create table test1(float_test float(5,2));   //一共5位，也就是整数+小数一共5位，同时小数最多2位

  mysql> insert into test1 values(1000.123);ERROR 1264 (22003): Out of range value for column ‘float_test‘ at row 1

  mysql> insert into test1 values(999.123);

  mysql> select * from test1;+------------+| float_test |+------------+|     999.12 |+------------+

(d) MySQL data type