MySQL数据类型
分类:MYSQL 热度:

 
MySQL数据类型
========================================================
一、MySQL常见的数据类型
在MySQL数据库管理系统中,可以通过存储引擎来决定表的类型。同时,MySQL数据库管理系统也
提供了数据类型决定表存储数据的类型。MySQL数据库管理系统提供的数据类型:
 
数值类型:
   整数类型      TINYINT SMALLINT MEDIUMINT INT BIGINT    
   浮点数类型    FLOAT DOUBLE
   定点数类型    DEC
   位类型        BIT
字符串类型:
   CHAR系列      CHAR  VARCHAR
        TEXT系列   TINYTEXT TEXT MEDIUMTEXT LONGTEXT
        BLOB 系列    TINYBLOB BLOB MEDIUMBLOB LONGBLOB 
        BINARY系列 BINARY VARBINARY 
时间和日期类型:    DATE TIME DATETIME TIMESTAMP YEAR 
枚举类型:    ENUM
集合类型:              SET
 
二、数据类型测试
===整数类型测试:tinyint,int
作用:用于存储用户的年龄、游戏的Level等。
mysql> create table test1(tinyint_test tinyint, int_test int);
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.01 sec)
 
mysql> insert into test1 values (111,111);
Query OK, 1 row affected (0.09 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(2147483647);
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1
 
//测试结果,默认有符号,存成了有符号的最大值
mysql> select * from test1;
+--------------+------------+
| tinyint_test | int_test   |
+--------------+------------+
|          111 |        111 |
|         NULL | 2147483647 |
|          -20 |        -20 |
+--------------+------------+
3 rows in set (0.00 sec)
 
mysql> create table test2( tinyint_test tinyint unsigned, //约束条件unsigned限定只能存正值(无符号)
        -> int_test int unsigned);
Query OK, 0 rows affected (0.00 sec)
mysql> desc test2;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| tinyint_test | tinyint(3) unsigned | YES  |     | NULL    |       | 
| int_test     | int(10) unsigned    | YES  |     | NULL    |       | 
+--------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)
mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
 
测试整数类型的显示宽度
mysql> create table t1 (id1 int,id2 int(6));
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | YES  |     | NULL    |       |
| id2   | int(6)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)
 
mysql> create table t2 (id1 int zerofill,id2 int(6) zerofill);
Query OK, 0 rows affected (0.05 sec)
mysql> desc t2;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id1   | int(10) unsigned zerofill | YES  |     | NULL    |       |
| id2   | int(6) unsigned zerofill  | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000002 | 000002 |
+------------+--------+
1 row in set (0.00 sec)
mysql> insert into t2 values(3,2222222); //插入大于宽度限制的值,仍然可以存储
Query OK, 1 row affected (0.03 sec)
mysql> select * from t2;
+------------+---------+
| id1        | id2     |
+------------+---------+
| 0000000002 |  000002 |
| 0000000003 | 2222222 |
+------------+---------+
2 rows in set (0.00 sec)
结论:整形的宽度仅为显示宽度,不是限制。因此建议整形无须指定宽度。
 
===浮点数类型测试:
作用:用于存储用户的身高、体重、薪水等
浮点数和定点数都可以用类型名称后加(M,D)的方式来表示,(M,D)表示一共显示M位数字(整数位
+小数位),其中D位于小数点后面,M和D又称为精度和标度。
float和double在不指定精度时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示,
而decimal在不指定精度时,默认的整数位为10,默认的小数位为0   
定点数在MySQL内部以字符串形式存储,比浮点数更精确,适合用来表示货币等精度高的数据。
 
mysql> create table test4(float_test float(5,2)); //一共5位,小数占2位
Query OK, 0 rows affected (0.00 sec)
mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES  |     | NULL    |       | 
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test4 values (10.2), (70.243), (70.246);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test4;
+------------+
| float_test |
+------------+
|      10.20 | 
|      70.24 | 
|      70.25 | 
+------------+
3 rows in set (0.00 sec)
mysql> insert into test4 values (1111.2);
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1
 
定点数decimal测试:
mysql>  create table test5(decimal_test decimal(5,2));
mysql> insert into test5 values (70.245);
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1265 | Data truncated for column 'decimal_test' at row 1 |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
 
===位类型测试:BIT
BIT(M)可以用来存放多位二进制数,M范围从1~64,如果不写默认为1位
对于位字段可以使用函数读取:
bin()显示为二进制
hex()显示为十六进制
mysql> create table test_bit (id bit(4));
Query OK, 0 rows affected (0.35 sec)
 
mysql> desc test_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(4) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.01 sec)
 
mysql> insert into test_bit values(1);
mysql> insert into test_bit values(15);
 
mysql> select * from test_bit;
+------+
| id   |
+------+
|     |
+------+
1 row in set (0.00 sec)
mysql> select bin(id),hex(id) from test_bit;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 100     | 4       |
+---------+---------+
1 row in set (0.00 sec)
 
插入超过指定宽度的值:
mysql> insert into test_bit values(8);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test_bit values(9);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test_bit values(16);
ERROR 1406 (22001): Data too long for column 'id' at row 1
 
===时间和日期类型测试:date、time、datetime、timestamp
作用:用于存储用户的注册时间,在线时长,文章的发布时间,员工的入职时间等
mysql> create table test_time(d date, t time,dt datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from test_time;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2013-12-18 | 00:06:10 | 2013-12-18 00:06:10 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
mysql> create table t(id timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)
mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t;
+---------------------+
| id                  |
+---------------------+
| 2013-12-18 00:08:41 |
+---------------------+
1 row in set (0.00 sec)
 
注意事项:
其它的时间,按要求插入
==插入年份时,尽量使用4位值
==插入两位年份时,<=69,以20开头,比如65,  结果2065      
                            >=70,以19开头,比如82,结果1982
 
mysql> create table t3(born_year year);
Query OK, 0 rows affected (0.40 sec)
mysql> desc t3;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| born_year | year(4) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t3 values (12),(80);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from t3;
+-----------+
| born_year |
+-----------+
|      2012 |
|      1980 |
+-----------+
2 rows in set (0.00 sec)
 
===字符串类型测试:CHAR、VARCHAR
作用:用于存储用户的姓名、爱好、发布的文章等
CHAR列的长度固定为创建表时声明的长度: 0 ~ 255
VARCHAR列中的值为可变长字符串,长度: 0 ~ 65535
注:在检索的时候,CHAR列删除了尾部的空格,而VARCHAR则保留这些空格
 
mysql> create table vc ( v varchar(4),c char(4));
Query OK, 0 rows affected (0.03 sec)
mysql> desc vc;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| v     | varchar(4) | YES  |     | NULL    |       |
| c     | char(4)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into vc values('ab   ','ab   ');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from vc;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)
mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)
mysql> select concat(v,'='), concat(c,'=') from vc; //在后面加字符'=',看的更清楚
+---------------+---------------+
| concat(v,'=') | concat(c,'=') |
+---------------+---------------+
| ab  =         | ab=           |
+---------------+---------------+
1 row in set (0.00 sec)
 
 
字符串类型测试:BINARY、VARBINARY
BINARY 和 VARBINARY类似于CHAR 和 VARCHAR,存储二进制字符
mysql> create table binary_t (c binary(3));
Query OK, 0 rows affected (0.03 sec)
mysql> desc binary_t;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c     | binary(3) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into binary_t set c='aaa';
Query OK, 1 row affected (0.00 sec)
mysql> select *,hex(c) from binary_t;
+------+--------+
| c    | hex(c) |
+------+--------+
| aaa  | 616161 |
+------+--------+
1 row in set (0.00 sec)
 
===ENUM类型即枚举类型、集合类型SET测试
字段的值只能在给定范围中选择    常见的是单选按钮和复选框
enum  单选    只能在给定的范围内选一个值,如性别 sex 男male/女female
set    多选        在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3...)
表school.student3                
    姓名 name    varchar(50)              
    性别 sex       enum('m','f')                    
    爱好 hobby   set('music','book','game','disc')
 
mysql> create table student3(
    ->  name varchar(50),
    -> sex enum('m','f'),
    -> hobby set('music','book','game','disc')
    -> );
Query OK, 0 rows affected (0.31 sec)
 
mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| name  | varchar(50)                       | YES  |     | NULL    |       |
| sex   | enum('m','f')                     | YES  |     | NULL    |       |
| hobby | set('music','book','game','disc') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
mysql> insert into student3 values
    -> ('tom','m','book,game');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from student3;
+------+------+-----------+
| name | sex  | hobby     |
+------+------+-----------+
| tom  | boy  | book,game | 
+------+------+-----------+
1 row in set (0.00 sec)
 
mysql>  insert into student3 values ('jack','m','film');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
 
mysql> show create table student3\G
*************************** 1. row ***************************
       Table: student3
Create Table: CREATE TABLE `student3` (
  `name` varchar(50) default NULL,
  `sex` enum('m','f') default NULL,
  `hobby` set('music','book','game','disc') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
========================================================
 
上一篇:MySQL数据库基本操作 下一篇:MySQL表操作
猜你喜欢
各种观点
热门排行
精彩图文