1、查看數(shù)據類型

mysql> help data type    //通過help對數(shù)據進行查看，以及使用的方法

2、MySQL常見的數(shù)據類型

　　整數(shù)int、定點小數(shù)dec、浮點數(shù)float、字符串varchar、時間-時區(qū)、布爾bool、位

前言========================================

　　在MySQL數(shù)據庫中，進行數(shù)據存儲時，難免會出現(xiàn)數(shù)據類型轉換，其中就包括：隱式類型轉換、顯示類型轉換

1、隱式類型轉換：有害、無害

①對于新建表列

　　數(shù)字：int、decimal

　　字符串：varchar

　　日期：date

②查select、增insert時，對于常量

　　數(shù)字：不要加引號(非一定)

　　字符串：一定要加上引號

　　日期：可以加上引號，也可以不加---最好是加上

2、顯式類型轉換：調用cast函數(shù)

格式：

　　cast(expr as type)

　　　　---將任意類型的表達式expr轉換成指定類型type的值

mysql> select cast(19950101 as date);+------------------------+
| cast(19950101 as date) |
+------------------------+
| 1995-01-01             |
+------------------------+

常見的類型轉換：

　　字符串char--->數(shù)字、日期

　　數(shù)字--->字符串、日期

　　日期date--->數(shù)字、字符串

===========================================

常用的數(shù)據類型：

一、整數(shù)：

　　每個整型類型可以指定一個最小顯示寬度(注意：這個寬度并不表示存儲的值有多大)

基本格式：

　　INT[(M)] [UNSIGNED] [ZEROFILL]

①M：指定顯示寬度(默認是M=10)，以實際的取值范圍為準，值超過顯示寬度，顯示寬度自動增加

②unsigned：無符號的，表示只允許正數(shù)，所以取值范圍取絕對值且最大值*2

　　　　(例：int unsigned range is 0 to 4294967295)

③zerofill: 填充0(如果聲明了zerofill，該列會自動設為unsigned)

影響數(shù)字的顯示方式：

　　如果一個數(shù)字的寬度小于所允許的最大寬度，這個值前面會用0填充

　　如果寬度大于所允許的最大寬度但不超過取值范圍，以實際的取值范圍為準，不填0；超出取值范圍的報錯不存儲。

示例：

mysql> create table t1(id int zerofill);　　#默認顯示寬度10mysql> insert into t1 values(-1);　　#取值范圍：0--4294967295ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t1 values(123);
mysql> insert into t1 values(12300);
mysql> insert into t1 values(1.123);

mysql> select * from t1;+------------+
| id         |
+------------+
| 0000000123 |
| 0000012300 |
| 0123456789 |
| 0000000001 |
+------------+

解析：因為建表的設置是int整數(shù)，小數(shù)點后的數(shù)會四舍五入

二、(定點)小數(shù)：　　decimal(dec)精確小數(shù)類型---精確數(shù)的存儲

　　同義詞：decimal=dec=numeric=fixed

基本格式：

　　DEC[(M[,D])] [UNSIGNED] [ZEROFILL]

①M：精度，（整數(shù)位數(shù)+小數(shù)位數(shù)）不包含小數(shù)點的數(shù)字總位數(shù)(1--30)，不指定默認為10

②D：小數(shù)位數(shù)，如果不指定，默認為0 

　　若位數(shù)不夠，小數(shù)末尾補零；若位數(shù)超預算(整數(shù)位數(shù)=M-D)，報錯。

示例：

mysql> create table t2(id dec(5,3) unsigned);##數(shù)字總位數(shù)5
##整數(shù)位數(shù)5-3=2(不夠沒關系；超出報錯)
##小數(shù)位數(shù)3(不夠補零；超出四舍五入)mysql> insert into t2 values(12.345);
mysql> insert into t2 values(1.23);
mysql> insert into t2 values(123);
ERROR 1264 (22003): Out of range value for column 'id' at row 1mysql> insert into t2 values(12.3456);
mysql> select * from t2;+--------+
| id     |
+--------+
| 12.345 |
|  1.230 |
| 12.346 |
+--------+

dec的小數(shù)位數(shù)是個定數(shù)；

三、浮點數(shù)：　　float/double單精度、雙精度浮點類型

基本格式：

　　float[(length) | (<display width>,<scale>)]

　　double[(precision) | (<display width>,<scale>)]

兩者區(qū)別在于值所保留的存儲空間數(shù)量不同

注意：

　　由于對一個浮點數(shù)字來說，可用的存儲空間有限，如果一個數(shù)字非常大或非常小，將存儲這個數(shù)字的近似值而不是實際值。

①使用一個參數(shù)來指定浮點類型

length(precision)：在一個浮點數(shù)據類型中可以指定長度，來確定具體的浮點類型

　　0~24：單精度float，從第7位有效位，進行四舍五入存儲

　　25~30：雙精度double，從第16位有效位，進行四舍五入存儲

mysql> create table t3(id float(8));

　　//m=8，說明是單精度，存儲6位的近似值（第7位四舍五入，后面為0）mysql> insert into t3 values(123);
mysql> insert into t3 values(1234567);
mysql> insert into t3 values(123456789);
mysql> insert into t3 values(123456789.123456789);

mysql> select * from t3;+-----------+
| id        |
+-----------+
| 123       |
| 1234570   |
| 123457000 |
| 123457000 |
+-----------+

②使用兩個參數(shù)來指定浮點類型

　　單精度float(m,d)：m表示精度（0~24），d表示小數(shù)位數(shù)

　　雙精度double(m,d)：m表示精度（25~30），d表示小數(shù)位數(shù)

具有寬度和小數(shù)位數(shù)的float的同義詞：real和float4；指定double=float(30)

示例：設一個字段定義為float(5,3)---單精度浮點，精度5，小數(shù)位3（和dec一樣）

mysql> create table t_3(id float(5,3) unsigned);##數(shù)字總位數(shù)5
##整數(shù)位數(shù)5-3=2(不夠沒關系；超出報錯)
##小數(shù)位數(shù)3(不夠補零；超出四舍五入)mysql> insert into t_3 values(123);
ERROR 1264 (22003): Out of range value for column 'id' at row 1mysql> insert into t_3 values(12.3);
mysql> insert into t_3 values(12.3456);
mysql> insert into t_3 values(1.23456);

mysql> select * from t_3;+--------+
| id     |
+--------+
| 12.300 |
| 12.346 |
| 1.235  |
+--------+

float的小數(shù)位數(shù)不是固定的；

超出存儲范圍，近似值進行存儲。 

四、字符串：　　varchar可變長字符串類型

　　1.char(n)：固定長度，最多255個字符

　　2.varchar(n)：固定長度，最多65535個字符

　　3.tinytext：可變長度，最多255個字符

　　4.text：可變長度，最多65535個字符

　　5.mediumtext：可變長度，最多2的24次方-1個字符

　　6.longtext：可變長度，最多2的32次方-1個字符

示例：

mysql> create table t4(name varchar(21));
mysql> insert into t4 values(123);
mysql> insert into t4 values(xiaoming);
ERROR 1054 (42S22): Unknown column 'xiaoming' in 'field list'　　#字符串要用引號引起來mysql> insert into t4 values('xiaoming');
mysql> insert into t4 values('zhang'san');
    '> ^C?。?！字符串中的單引號用單引號進行轉義mysql> insert into t4 values('zhang''san');

mysql> select * from t4;+-----------+
| name      |
+-----------+
| 123       |
| xiaoming  |
| zhang'san |+-----------+

若要存儲中文字符串，需要進行字符設置

create table tableName(列名 varchar(20) character set utf8);

create table tableName(列名 varchar(20) character set gpk);

1、char和varchar的區(qū)別：

　?、賑har(n) 若存入字符數(shù)小于n，則以空格補于其后，查詢之時再將空格去掉。所以char類型存儲的字符串末尾不能有空格，varchar不限于此。

　?、赾har(n) 固定長度，char(4)不管是存入幾個字符，都將占用4個字節(jié)，varchar是存入的實際字符數(shù)+1個字節(jié)（n<=255）或2個字節(jié)(n>255)，所以varchar(4),存入3個字符將占用4個字節(jié)。

　　③char類型的字符串檢索速度要比varchar類型的快。

2、varchar和text的區(qū)別：

　?、賤archar可指定n，text不能指定，內部存儲varchar是存入的實際字符數(shù)+1個字節(jié)（n<=255）或2個字節(jié)(n>255)，text是實際字符數(shù)+2個字節(jié)。

　?、趖ext類型不能有默認值。

　?、踲archar可直接創(chuàng)建索引，text創(chuàng)建索引要指定前多少個字符。varchar查詢速度快于text,在都創(chuàng)建索引的情況下，text的索引似乎不起作用。

五、日期時間時區(qū)

　　date：日期 '2008-12-02'

　　time：時間 '12:25:36'

　　datetime：日期時間 '2008-12-12 22:06:44'

　　timestamp：自動存儲記錄修改時間（如果數(shù)據庫里面有timestamp數(shù)據類型，就應該考慮時區(qū)問題）

示例：

mysql> create table t5(d1 date);
mysql> insert into t5 values(19901101);　　#推薦的正統(tǒng)寫法mysql> insert into t5 values(901101);
mysql> insert into t5 values(1990111);　　#有問題的寫法mysql> select * from t5;+------------+
| d1         |
+------------+
| 1990-11-01 |
| 1990-11-01 |
| 0199-01-11 |
+------------+

1、獲得當前日期時間的函數(shù)

①獲得當前日期+時間(date+time)函數(shù)：now()、sysdate()

區(qū)別：now()在執(zhí)行開始時值就得到了，sysdate()在函數(shù)執(zhí)行時動態(tài)得到值

②獲得當前日期curdate()、獲得當前時間curtime()

mysql> select curdate(),curtime();+------------+-----------+
| curdate()  | curtime() |
+------------+-----------+
| 2017-03-21 | 11:00:25  |
+------------+-----------+

========================================

1）日期字符串轉換函數(shù)format：

　　date_format(date,format)

---將日期date按照給定的模式format轉換成字符串

　　time_format(time,format)

---將時間time按照給定的模式format轉換成字符串，format中只可以使用時、分、秒、微秒模式元素

format中可使用的模式元素可查官方文檔

2）日期字符串轉換函數(shù)

　　str_to_date(str,format)

---將字符串str以指定的模式format轉換成日期

mysql> select str_to_date('01,5,2013','%d,%m,%Y');

mysql> select str_to_date('May 1, 2013','%M %d,%Y');

如果在會話變量@@sql_mode設置中包含no_zero_date和no_zero_in_date時，上述轉換將會失敗，沒有包含這些設置時則轉換成功。

======================================== 

③獲得當前 UTC 日期時間函數(shù)：utc_date(), utc_time(), utc_timestamp()

2、MySQL時區(qū)問題：重點?。。?/span>

　　注意：當數(shù)據庫里面有timestamp數(shù)據類型的列，就應該考慮時區(qū)參數(shù)

問：如何判斷是否有timestamp數(shù)據類型的列？

答：

mysql> select table_name,column_name,data_type

　　-> from information_schema.columns

　　-> where

　　-> data_type='timestamp';

　　……

　　MySQL是安裝在Linux操作系統(tǒng)下面，因此MySQL時區(qū)跟Linux的時區(qū)有很多關聯(lián)的地方，MySQL里有一個時區(qū)叫做系統(tǒng)時區(qū)，MySQL系統(tǒng)時區(qū)的存在是為MySQL安裝后讀取Linux操作系統(tǒng)的時區(qū)，將自己的系統(tǒng)時區(qū)設置為與Linux操作系統(tǒng)一致 的時區(qū)：

mysql> show variables like "system_time%";    #查看系統(tǒng)時區(qū)

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

| Variable_name    | Value |

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

| system_time_zone | CST   |

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

Linux 操作系統(tǒng)的時區(qū)（有很多選擇）與 mysql 中的系統(tǒng)時區(qū)是一致的：

　　Linux操作系統(tǒng)的時區(qū)

　　　　shell> cat /etc/sysconfig/clock

　　區(qū)域信息目錄：/usr/share/zoneinfo

系統(tǒng)時區(qū)的選擇對后續(xù) mysql 的操作影響不大，但是，MySQL中的time_zone的全局時區(qū)，會對我們的MySQL產生影響：

mysql> show variables like "time_zone%";　　#查看數(shù)據庫時區(qū)

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

| Variable_name    | Value  |

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

| time_zone        | SYSTEM |

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

問：為什么說time_zone正確與否會對MySQL產生影響？（也就是timestamp列的工作過程）

答：

　　①當有timestamp列是，一個會話連接上來，這個會話默認的時區(qū)就等于time_zone的時區(qū)，當然會話時區(qū)也可以被修改。

　?、谕鵷imestamp列里存數(shù)據的時候，mysql會先查詢存數(shù)據的會話所在的時區(qū)，mysql把會話對應的時區(qū)與存儲的時間結合換算出時間所對應的0時區(qū)，再把這個時間存儲到timestamp列中，所以timestamp列存儲都是相對于0時區(qū)的時間。

　　③當用戶查詢數(shù)據的時候，mysql會將timestamp列中相對應于0時區(qū)的數(shù)據轉換成會話對應的時區(qū)，反應給用戶。

　　④當會話對應的時區(qū)與存儲時間的時區(qū)不對應時，存儲的時間仍然會被轉換為0時區(qū)對應的時間進行存儲，那么這時我們存儲的時間就是錯誤的，所以我們必須保證會話時區(qū)與存儲時間的時區(qū)一致。

mysql> select @@global.time_zone;    #查看全局時區(qū)

mysql> select @@session.time_zone;    #查看會話時區(qū)(默認等于全局時區(qū))

1.臨時設置時區(qū)(全局)：

①'+8:00'格式

　　mysql> set @@global.time_zone='+8:00';

②'Asia/Shanghai'格式

　　mysql> set @@global.time_zone='Asia/Shanghai';

　　ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Shanghai'

加載系統(tǒng)時區(qū)：

　　shell> mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -uroot -p123 mysql

　　mysql> set @@global.time_zone='Asia/Shanghai';

2.永久設置時區(qū)：

修改配置文件vim /etc/my.cnf

[mysqld]區(qū)域中添加default_time_zone=Asia/Shanghai

時區(qū)的正確實踐（只有timestamp數(shù)據類型的才有意義）：

　?、賗nsert以前，你的values對應的時間到底是哪個時區(qū)，然后設置set @@session.time_zone為對應的時區(qū)

　?、趕elect獲取以前，你想得到什么時區(qū)的時間，就設置set @@session.time_zone為對應的時區(qū)。

示例：

會話1：當前會話

mysql> select @@global.time_zone;+--------------------+
| @@global.time_zone |
+--------------------+
| +08:00             |
+--------------------+mysql> create table t9(tm timestamp);

mysql> insert into t9 values('2017-3-22 10:10');

mysql> select * from t9;+---------------------+
| tm                  |
+---------------------+
| 2017-03-22 10:10:00 |
+---------------------+

會話2：客戶端登錄會話(東10區(qū)用戶)

設置會話時區(qū)

mysql> set @@session.time_zone='+10:00';

mysql> use test; select * from t9;
Database changed+---------------------+
| tm                  |
+---------------------+
| 2017-03-22 12:10:00 |
+---------------------+

六、布爾bool或boolean=tinyint(1)

　　A value of zero is considered false,nonzero values are considered true.

示例：

mysql> create table t7(num bool);

mysql> insert into t7 values(true);
mysql> insert into t7 values(false);
mysql> insert into t7 values(123);
mysql> insert into t7 values(1);
mysql> insert into t7 values(0);
mysql> insert into t7 values(128);    #范圍在-128~127ERROR 1264 (22003): Out of range value for column 'num' at row 1mysql> select * from t7;+------+
| num  |
+------+
|    1 |
|    0 |
|  123 |
|    1 |
|    0 |
+------+

七、位

　　位主要是用來存放二進制數(shù)，select查詢默認顯示10進制，可以使用bin()、hex()函數(shù)來進行查詢

bin()---顯示二進制格式

hex()---顯示十六進制格式

示例：

mysql> create table t7(id bit(4));

mysql> insert into t7 values(2);
mysql> insert into t7 values(13);

mysql> select bin(id),hex(id) from t7;+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 10      | 2       |
| 1101    | D       |
+---------+---------+

@author：http://www.cnblogs.com/geaozhang/

http://www.cnblogs.com/geaozhang/p/6718773.html

延伸閱讀

• 崔博士 2018-08-09
• 集合結合數(shù)據結構來看看(二) 2017-07-26
• EF查詢百萬級數(shù)據的性能測試--單表查詢 2017-07-26
• MySQL數(shù)據庫Raid存儲方案 2017-07-26
• net.sz.framework 框架 ORM 消消樂超過億條數(shù)據排行榜分析 天王蓋地虎 序言 2017-07-26
• MS MDS系列之初識MS Master Data Service(微軟主數(shù)據服務) 背景介紹： 2017-07-26
• 計算機程序的思維邏輯 (92) - 函數(shù)式數(shù)據處理 (上) 2017-07-26
• Java開源博客My-Blog之mysql容器重復初始化的嚴重bug修復過程 2017-07-26
• 數(shù)據結構3——淺談zkw線段樹 2017-07-25
• Pandas數(shù)據處理實戰(zhàn)：福布斯全球上市企業(yè)排行榜數(shù)據整理 2017-07-25

崔博士