mysql里面有没有map类型_MySQL学习（二） 数据类型

MySQL支持多种列类型：数值类型、日期/时间类型和字符串(字符)类型。

数值类型

数值类型又分为整数型与小数型

整数型

下面的表显示了需要的每个整数类型的存储和范围

创建一张表

mysql> CREATE TABLE t_int (

int_1 TINYINT,

int_2 SMALLINT,

int_3 MEDIUMINT,

int_4 INT,

int_5 BIGINT);

Query OK, 0 rows affected

mysql> DESC t_int;

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

| Field | Type | Null | Key | Default | Extra |

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

| int_1 | tinyint(4) | YES | | NULL | |

| int_2 | smallint(6) | YES | | NULL | |

| int_3 | mediumint(9) | YES | | NULL | |

| int_4 | int(11) | YES | | NULL | |

| int_5 | bigint(20) | YES | | NULL | |

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

5 rows in set

插入数据，只能插入整型，数据类型不正确或者超出范围都会导致插入数据失败

mysql> INSERT INTO t_int VALUES(100, 100, 100, 100, 100);

Query OK, 1 row affected

mysql> INSERT INTO t_int VALUES('a', 'b', 100, 200, 300); -- 数据类型不正确

1366 - Incorrect integer value: 'a' for column 'int_1' at row 1

mysql> INSERT INTO t_int VALUES(255, 10000, 1000, 100000, 1000000); -- 超出范围

1264 - Out of range value for column 'int_1' at row 1

mysql> SELECT * FROM t_int;

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

| int_1 | int_2 | int_3 | int_4 | int_5 |

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

| 100 | 100 | 100 | 100 | 100 |

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

1 row in set

SQL中的数值类型全部都是默认有符号，分正负，如果要使用无符号数据，则需要用UNSIGNED关键字对数据类型进行限定

mysql> ALTER TABLE t_int ADD COLUMN int_6 TINYINT UNSIGNED; -- 无符号类型

Query OK, 1 row affected

Records: 1 Duplicates: 0 Warnings: 0

mysql> DESC t_int;

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

| Field | Type | Null | Key | Default | Extra |

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

| int_1 | tinyint(4) | YES | | NULL | |

| int_2 | smallint(6) | YES | | NULL | |

| int_3 | mediumint(9) | YES | | NULL | |

| int_4 | int(11) | YES | | NULL | |

| int_5 | bigint(20) | YES | | NULL | |

| int_6 | tinyint(3) unsigned | YES | | NULL | |

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

6 rows in set

mysql> INSERT INTO t_int VALUES(127, 10000, 100000, 100000, 1000000, 255); -- TINYINT无符号类型的取值范围为0-255

Query OK, 1 row affected

mysql> SELECT * FROM t_int;

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

| int_1 | int_2 | int_3 | int_4 | int_5 | int_6 |

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

| 100 | 100 | 100 | 100 | 100 | NULL |

| 127 | 10000 | 100000 | 100000 | 1000000 | 255 |

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

2 rows in set

给字段加了无符号的限定后，数据插入成功了，在查看表结构的时候，发现每个字段的数据类型之后都会自带一个括号，里面有指定的数字，这个数字表示数据最终显示的位数，例如-123表示显示4位(包括符号位)，255表示显示3位，实际上这个数字没有什么特别的含义，只是默认告诉用户可以显示的宽度而已，并不会改变数据的大小。

mysql> ALTER TABLE t_int ADD COLUMN int_7 TINYINT(1) UNSIGNED; -- 显示指定显示宽度

Query OK, 2 rows affected

Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO t_int VALUES(127, 10000, 100000, 100000, 1000000, 255, 255);

Query OK, 1 row affected

mysql> SELECT * FROM t_int;

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

| int_1 | int_2 | int_3 | int_4 | int_5 | int_6 | int_7 |

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

| 100 | 100 | 100 | 100 | 100 | NULL | NULL |

| 127 | 10000 | 100000 | 100000 | 1000000 | 255 | NULL |

| 127 | 10000 | 100000 | 100000 | 1000000 | 255 | 255 |

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

3 rows