mysql笔记-- mysql命令札记7 数据类型

最新推荐文章于 2014-04-03 15:42:03 发布

原创最新推荐文章于 2014-04-03 15:42:03 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

11 篇文章

订阅专栏

本文详细介绍了MySQL中数字类型、字符串类型、日期和时间类型的特点与使用注意事项，并提供了关键信息的概述。

Mysql 的数据类型

在使用数据库中，需要尽可能的减小数据的类型，因此合理的选用数据定义的类型是很重要的，数据的类型就决定了数据库的大小；

NOTE: 在mysql 中，字段名称是部分大小写的。但是数据库名称和数据表名称是区分大小写的；

7.1 数字类型

数字类型分为整型和浮点型两大类，此处不再赘述；

NOTE: 所有的数字类型允许两个选项 UNSIGNED & ZEROFILL；

UNSIGNED 表示数据不为负数；

ZEROFILL 表述数据为O；并且自动变为UNSIGNED;

7.1.1 数字类型列表

7.1.2实例

mysql> CREATE TABLE test
-> (tiny_int TINYINT(1),
-> bool_int BOOL,
-> bit_int BIT,
-> small_int SMALLINT(1),
-> medium_int MEDIUMINT(1),
-> int_int INT(1),
-> big_int BIGINT(1),
-> integer_int INTEGER);
Query OK, 0 rows affected (0.07 sec)
mysql> desc test;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| tiny_int | tinyint(1) | YES | | NULL | |
| bool_int | tinyint(1) | YES | | NULL | |
| bit_int | bit(1) | YES | | NULL | |
| small_int | smallint(1) | YES | | NULL | |
| medium_int | mediumint(1) | YES | | NULL | |
| int_int | int(1) | YES | | NULL | |
| big_int | bigint(1) | YES | | NULL | |
| integer_int | int(11) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.09 sec)

mysql> insert into test (tiny_int, bool_int, bit_int, small_int, medium_int, int_int, big_int, integer_int) values (100000000,10000000,1000,1000,1000,1000,1000,1000), (-1000,-1000,-1000,-1000,-1000,-1000,-1000,-1000)\c
mysql> insert into test (tiny_int, bool_int, bit_int, small_int, medium_int,
-> int_int, big_int, integer_int)
-> values
-> (9999999999999999999999999999,9999999999999999999999999999,9999999999999999999999999999,9999999999999999999999999999,
-> 9999999999999999999999999999,9999999999999999999999999999,9999999999999999999999999999,9999999999999999999999999999),
-> (-9999999999999999999999999999,-9999999999999999999999999999,-9999999999999999999999999999,-9999999999999999999999999999,
-> -9999999999999999999999999999,-9999999999999999999999999999,-9999999999999999999999999999,-9999999999999999999999999999);
Query OK, 2 rows affected, 29 warnings (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 29

mysql> select * from test;
+----------+----------+---------+-----------+------------+-------------+----------------------+-------------+-------------+
| tiny_int | bool_int | bit_int | small_int | medium_int | int_int | big_int | integer_int |
+----------+----------+---------+-----------+------------+-------------+----------------------+-------------+-------------+
| 127 | 127 | | 32767 | 8388607 | 2147483647 | 9223372036854775807 | 2147483647 |
| -128 | -128 | | -32768 | -8388608 | -2147483648 | -9223372036854775808 | -2147483648 |
+----------+----------+---------+-----------+------------+-------------+----------------------+-------------+-------------+
4 rows in set (0.00 sec)