33_01_2_MySQL数据类型

2016-03-04

说明：没有特殊说明的话，
Mariadb的版本为:

MariaDB [mydb]> select version();

+------------------------------+ 
| version()                    | 
+------------------------------+ 
| 10.1.11-MariaDB-1~trusty-log | 
+------------------------------+

MySQL的版本:

mysql> select version();

+------------+ 
| version()  | 
+------------+ 
| 5.6.29-log | 
+------------+

数据类型:
ISO SQL:2003 standard

CHAR(N): 0 to 255
VARCHAR(N): 0 to 65535

char(N)一旦分配，就固定了;

char(4)超过后怎么处理?
sql_mode

MariaDB [mydb]> SHOW VARIABLES LIKE 'sql_mode'; 
+---------------+--------------------------------------------+ 
| Variable_name | Value                                      | 
+---------------+--------------------------------------------+ 
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | 
+---------------+--------------------------------------------+ 
 
MariaDB [mydb]> CREATE TABLE t1( col1 char(3), col2 varchar(3)); 
MariaDB [mydb]> insert into t1(col1,col2)values('abcd','abcd'); 
MariaDB [mydb]> show warnings; 
+---------+------+-------------------------------------------+ 
| Level   | Code | Message                                   | 
+---------+------+-------------------------------------------+ 
| Warning | 1265 | Data truncated for column 'col1' at row 1 | 
| Warning | 1265 | Data truncated for column 'col2' at row 1 | 
+---------+------+-------------------------------------------+ 
 
 
 
mysql> SELECT @@sql_mode; 
+--------------------------------------------+ 
| @@sql_mode                                 | 
+--------------------------------------------+ 
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | 
+--------------------------------------------+ 
mysql> insert into t1(col1,col2)values('abcd','abcd'); 
ERROR 1406 (22001): Data too long for column 'col1' at row 1 
直接无法插入; 
 
 
varchar(N)：N+1个字节 
 
MariaDB [mydb]> create table t1( col1 varchar(65500)); 
MariaDB [mydb]> desc t1; 
+-------+------------+------+-----+---------+-------+ 
| Field | Type       | Null | Key | Default | Extra | 
+-------+------------+------+-----+---------+-------+ 
| col1  | mediumtext | YES  |     | NULL    |       | 
+-------+------------+------+-----+---------+-------+ 
MariaDB会将其自动转换为mediumtext类型;

对于MySQL来说：
ERROR 1074 (42000): Column length too big for column ‘col1’ (max = 21845); use BLOB or TEXT instead

binary类型:
binary –>和char相似，不过区别一个是以二进制的方式进行存储
varbinary –>varchar，这个和varchar相似;

MariaDB [test]> CREATE TABLE t1( col1 binary(4)); 
MariaDB [test]> INSERT INTO t1 VALUES('a'); 
MariaDB [test]> SELECT col1='a\0\0\0' FROM t1; 
+----------------+ 
| col1='a\0\0\0' | 
+----------------+ 
|              1 | 
+----------------+ 
 
SELECT col1='a\0\0\0' 表示由于binary是定长，那么需要在后面补0补成定义的长度，如果相等，那么则返回1,如果不等，则返回0; 
 
mysql> CREATE TABLE t1_bin( col1 binary(5)); 
mysql> select col1='a\0\0\0\0' from t1_bin; 
+------------------+ 
| col1='a\0\0\0\0' | 
+------------------+ 
|                1 | 
+------------------+

BLOB类型和TEXT类型:

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT

ENUM类型:
在MariaDB中是非严格类型：

1
2
3

MariaDB [test]> create table enum_a( col1 enum('a','b','c')); 
MariaDB [test]> insert into enum_a values('d'); 
MariaDB [test]> select * from enum_a;

会显示为空，以及有警告;
而在MySQL中则会直接报错，无法进行插入;

MariaDB [test]> insert into enum_a values('c'); 
MariaDB [test]> select col1+0 from enum_a; 
+--------+ 
| col1+0 | 
+--------+ 
|      0 | 
|      3 | 
+--------+ 
 
列+0表示他在enum中的那个位置; 
 
SET类型：最大64个； 
MariaDB [test]> CREATE TABLE set_t(col1 set('F','M','U')); 
MariaDB [test]> INSERT INTO set_t VALUES('F'); 
MariaDB [test]> INSERT INTO set_t VALUES('F,U'); 
MariaDB [test]> select * from set_t; 
+------+ 
| col1 | 
+------+ 
| F    | 
| F,U  | 
+------+

SET和ENUM类型的区别在于SET可以插入多个指定的值，而ENUM不能;

日期时间类型:

DATETIME: 8个字节 ‘1000-01-01 00:00:00’ to ‘9999-12-31 23-59:59’
DATE：3个字节 ‘1000-01-01’ to ‘9999-12-31’
TIME：3个字节 ‘-838:59:59’ to ‘838:89:89’
TIMESTAMP ‘1970-01-01 00:00:00’ to mid-year 2037
YEAR： 1901 to 2155(for YEAR(4)) 1970 to 2069 (for YEAR(2))

TIMESTAMP主要应用于自动更新地方;

例如记录最后登录时间:

MariaDB [mydb]> create table time_t1 ( 
    -> id smallint, 
    -> col timestamp default current_timestamp 
    -> on update current_timestamp); 
 
MariaDB [mydb]> insert into time_t1(id,col)values(100,Now()); 
MariaDB [mydb]> select * from time_t1; 
+------+---------------------+ 
| id   | col                 | 
+------+---------------------+ 
|  100 | 2016-02-21 17:35:58 | 
+------+---------------------+ 
MariaDB [mydb]> update  time_t1 set id=101 where id=100; 
MariaDB [mydb]> select * from time_t1; 
+------+---------------------+ 
| id   | col                 | 
+------+---------------------+ 
|  101 | 2016-02-21 17:36:36 | 
+------+---------------------+

会发现这个时间发生了改变；;

use test; 
 
#时间 
drop table if exists date_t1; 
create table date_t1 ( date_type datetime); 
 
insert into date_t1 values (Now()); 
insert into date_t1 values (current_timestamp()); 
 
#日期 
drop table if exists date_t2; 
create table date_t2 (date_type date); 
insert into date_t2 values (current_date()); 
 
#当前时间 
drop table if exists date_t3; 
create table date_t3 (date_type time); 
insert into date_t3 values (current_time()); 
insert into date_t3 values (current_timestamp()); 
 
select * from date_t1; 
select * from date_t2; 
select * from date_t3;

载入系统时区至mysql timezone中:

x180@x450vc:~$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -uroot -p mysql

会发现mysql.time_zone和mysql.time_zone_name会有内容了;

create table tz_t1( 
    login_d timestamp default current_timestamp 
); 
 
show variables like 'time_zone'; 
insert into tz_t1 values(Now()); 
set time_zone='America/Los_Angeles'; 
insert into tz_t1 values(Now());

关于concat的基本使用:
在col1列的所有字段增加一个-号，col2增加一个+号:

SELECT CONCAT(col1,'-'),CONCAT(col2,'+') FROM tb1; 
 
MariaDB [mydb]> select concat(Site,'-'),concat(User,'+') FROM passwd; 
+------------------+------------------+ 
| concat(Site,'-') | concat(User,'+') | 
+------------------+------------------+ 
| QQ-              | 1103639070+      | 
| steam-           | Dragon+          | 
| steam-           | M_x180+          | 
+------------------+------------------+

问题一

关于全文索引的问题
MariaDB和MySQL的InnoDB存储引擎都支持全文索引，但是有条件：

+----+-----------------------------------------+ 
| id | body                                    | 
+----+-----------------------------------------+ 
|  1 | afadfdaghkk                             | 
|  2 | i am a good body                        | 
|  3 | i am a bad body                         | 
|  4 | wo cao,shen me qingkuang                | 
|  5 | wocao,shen me qingkuang                 | 
|  6 | 卧槽，什么情况                          | 
|  7 | 卧槽，什么情况啊                        | 
|  8 | 卧槽额，什么情况啊                      | 
|  9 | 卧槽，什么情况啊，这个                  | 
| 10 | 卧槽 什么情况啊 这个                    | 
| 11 | 卧槽额额，什么情况啊，这个              | 
+----+-----------------------------------------+ 
MariaDB [test]> select * from art where match(body) against('卧槽额'); 
+----+-----------------------------+ 
| id | body                        | 
+----+-----------------------------+ 
|  8 | 卧槽额，什么情况啊          | 
+----+-----------------------------+ 
MariaDB [test]> select * from art where match(body) against('卧槽额额'); 
+----+-----------------------------------------+ 
| id | body                                    | 
+----+-----------------------------------------+ 
| 11 | 卧槽额额，什么情况啊，这个              | 
+----+-----------------------------------------+ 
MariaDB [test]> select * from art where match(body) against('什么情况'); 
+----+-----------------------+ 
| id | body                  | 
+----+-----------------------+ 
|  6 | 卧槽，什么情况        | 
+----+-----------------------+ 
MariaDB [test]> select * from art where match(body) against('什么情况啊'); 
+----+-----------------------------------------+ 
| id | body                                    | 
+----+-----------------------------------------+ 
|  7 | 卧槽，什么情况啊                        | 
|  8 | 卧槽额，什么情况啊                      | 
|  9 | 卧槽，什么情况啊，这个                  | 
| 10 | 卧槽 什么情况啊 这个                    | 
| 11 | 卧槽额额，什么情况啊，这个              | 
+----+-----------------------------------------+

(1)在插入数据的时候，如:iam 和 i am,是两个不同的存储，在匹配的时候，是以空格作为分割符进行匹配，如果想搜索am，只会出来 i am，而不会出来iam;
(2)支持中文全文索引，但是支持力度不好:
如上：必须对中文进行精确查询，而且中文个数最好在3个以上；
而且前提是必须有分隔符，没有分隔符将不会查出来；

对于innodb 全文索引来讲，感觉不是那么的明显，另外在mysql 5.6版本，增加了Online DDL,可以在线DDL,这个对于
DBA来讲非常的好，但是目前对于Online DDL不是太了解，另外在并发量比较大的时候，增加fulltext是不允许写的，所以
建议在访问量小的时候进行修改；

另外在alter table add fulltext的时候有一个警告:
17:19:16 alter table full_t1 add fulltext(content) 0 row(s) affected, 1 warning(s): 124 InnoDB rebuilding table to add column FTS_DOC_ID Records: 0 Duplicates: 0 Warnings: 1 0.125 sec

我在网上看到可以：

1
2
3

drop table if exists full_t1; 
create table full_t1 (FTS_DOC_ID int primary key auto_increment not null,content text)engine=innodb; 
alter table  full_t1 add fulltext(content);

但是这样不行，总之，对于Online DDL来讲，一定要在并发量小的情况下进行修改；

关于concat的问题
关于blob和text的问题
TEXT与BLOB的主要差别就是BLOB保存二进制数据，TEXT保存字符数据
关于timezone修改时区的问题