数据库教程:MySQL 数据类型详情

目录

• 1、数值类型
• 1.1、数值类型分类
• 1.1.1、浮点数
• 1.1.2、位类型
• 1.1.3、时间日期类型
• 1.1.4、字符串类型
• 1.1.5、enum 类型
• 1.1.6、set类型

  mysql> desc t2;  +-------+--------+------+-----+---------+-------+  | field | type   | null | key | default | extra |  +-------+--------+------+-----+---------+-------+  | id    | bit(1) | yes  |     | null    |       |  +-------+--------+------+-----+---------+-------+  1 row in set (0.00 sec)    mysql> insert into t2 values(1);  query ok, 1 row affected (0.05 sec)    mysql> select * from t2;  +------+  | id   |  +------+  | 
    |  +------+  1 row in set (0.00 sec)    mysql> select bin(id),hex(id) from t2;  +---------+---------+  | bin(id) | hex(id) |  +---------+---------+  | 1       | 1       |  +---------+---------+  1 row in set (0.03 sec)    

  mysql> insert into t2 values(2);  error 1406 (22001): data too long for column 'id' at row 1  mysql> alter table t2 modify id bit(2);  query ok, 1 row affected (0.67 sec)  records: 1  duplicates: 0  warnings: 0    mysql> insert into t2 values(2);  query ok, 1 row affected (0.03 sec)    mysql> select bin(id),hex(id) from t2;  +---------+---------+  | bin(id) | hex(id) |  +---------+---------+  | 1       | 1       |  | 10      | 2       |  +---------+---------+  2 rows in set (0.00 sec)    

  mysql> create table dt(d date,t time,dt datetime);  query ok, 0 rows affected (0.23 sec)      mysql> insert into dt values(now(),now(),now());  query ok, 1 row affected, 1 warning (0.05 sec)    mysql> select * from dt;  +------------+----------+---------------------+  | d          | t        | dt                  |  +------------+----------+---------------------+  | 2021-05-13 | 10:14:07 | 2021-05-13 10:14:07 |  +------------+----------+---------------------+  1 row in set (0.00 sec)    

  mysql> create table t(id1 timestamp);  query ok, 0 rows affected (0.22 sec)    mysql> insert into t values(null);  query ok, 1 row affected (0.05 sec)    mysql> select * from t;  +---------------------+  | id1                 |  +---------------------+  | 2021-05-13 10:18:05 |  +---------------------+  1 row in set (0.00 sec)    

  mysql> alter table t add column id2 timestamp;  query ok, 0 rows affected (0.48 sec)  records: 0  duplicates: 0  warnings: 0    mysql> show create table t g;  *************************** 1. row ***************************         table: t  create table: create table `t` (    `id1` timestamp not null default current_timestamp on update current_timestamp,    `id2` timestamp not null default '0000-00-00 00:00:00'  ) engine=innodb default charset=latin1  1 row in set (0.00 sec)    error:  no query specified    

  mysql> create table varc(v varchar(4),c char(4));  query ok, 0 rows affected (0.20 sec)    mysql> insert into varc values('abc ','abc ');  query ok, 1 row affected (0.03 sec)    mysql> select length(v),length(c) from varc;  +-----------+-----------+  | length(v) | length(c) |  +-----------+-----------+  |         4 |         3 |  +-----------+-----------+  1 row in set (0.01 sec)  mysql> select concat(v,'+'),concat(c,'+') from varc;  +---------------+---------------+  | concat(v,'+') | concat(c,'+') |  +---------------+---------------+  | abc +         | abc+          |  +---------------+---------------+  1 row in set (0.00 sec)    

  mysql> create table bina(c binary(3));  query ok, 0 rows affected (0.22 sec)    mysql> insert into t set c='a';  error 1054 (42s22): unknown column 'c' in 'field list'  mysql> insert into bina set c='a';  query ok, 1 row affected (0.05 sec)    mysql> select *,hex(c),c='a',c='a',c='a' from bina;  +------+--------+-------+---------+-----------+  | c    | hex(c) | c='a' | c='a' | c='a' |  +------+--------+-------+---------+-----------+  | a    | 610000 |     0 |       0 |         1 |  +------+--------+-------+---------+-----------+  1 row in set (0.00 sec)    

  mysql> create table gend(gender enum('m','f'));  query ok, 0 rows affected (0.20 sec)    mysql> insert into gend values('m'),('f'),('1'),(null);  query ok, 4 rows affected (0.03 sec)  records: 4  duplicates: 0  warnings: 0    mysql> select * from gend;  +--------+  | gender |  +--------+  | m      |  | f      |  | m      |  | null   |  +--------+  4 rows in set (0.00 sec)    

  mysql> create table st(col set('a','b','c','d'));  query ok, 0 rows affected (0.20 sec)    mysql> insert into st values('a,b'),('a,d,a'),('a,c'),('a');  query ok, 4 rows affected (0.03 sec)  records: 4  duplicates: 0  warnings: 0    mysql> insert into st values('f');  error 1265 (01000): data truncated for column 'col' at row 1  mysql> insert into st values(null);  query ok, 1 row affected (0.03 sec)    mysql> select * from st;  +------+  | col  |  +------+  | a,b  |  | a,d  |  | a,c  |  | a    |  | null |  +------+  5 rows in set (0.00 sec)