mysql表設計—-列類型介紹

author:skate
time:2012/02/18

 

mysql表設計-------列類型介紹

 

在我們設計資料庫時一些經驗，合理的評估表的增量，預計大小，分庫分表、整庫表的數量(為了方便維護與效能，mysql庫一般500張表以裡)等。

從基本的說起，我們建立表除了考慮架構上表的設計原則，更要從基本考慮，這對於大系統來說非常重要
命名規則：合理規劃整庫的命名規則，這樣方便開發與維護
列類型的選擇：這個對於sql的最佳化，資料存放區空間等都非常重要，下面就簡單介紹下mysql列類型

 

數實值型別:

BIT(M):
位欄位類型。M表示每個值的位元，範圍為從1到64。M預設為1。

TinyINT(M):
帶符號的範圍是-32768到32767。無符號的範圍是0到65535。1位元組。

SmallINT(M):
帶符號的範圍是-32768到32767。無符號的範圍是0到65535。2位元組。

MediumINT(M):
帶符號的範圍是-8388608到8388607。無符號的範圍是0到16777215。3位元組。

INT(M):
帶符號的範圍是-2147483648到2147483647。無符號的範圍是0到4294967295。4位元組。

 

BigINT(n):
帶符號的範圍是-9223372036854775808到9223372036854775807。無符號的範圍是0到18446744073709551615。8位元組。

Decimal(m,n): M是總位元，D是小數點(標度)後面的位元。實際上是VARCHAR儲存。

FLOAT(M,D):
小(單精確度)浮點數,M是小數縱位元，D是小數點後面的位元。如果M和D被省略，根據硬體允許的限制來儲存值。單精確度浮點數精確到大約7位小數位,帶符號的範圍是 -3.402823466E+38 to -1.175494351E-38,無符號的範圍是0 and 1.175494351E-38 to 3.402823466E+38, 4 位元組

DOUBLE(M,D):
普通大小(雙精確度)浮點數,M是小數總位元，D是小數點後面的位元。如果M和D被省略，根據硬體允許的限制來儲存值。雙精確度浮點數精確到大約15位小數位,帶符號的範圍是 -1.7976931348623157E+308 to -2.2250738585072014E-308,無符號的範圍是0 and 2.2250738585072014E-308 to 1.7976931348623157E+308,8 位元組

 

 

數實值型別處理函數:
算數符運算：SELECT expr;
數學函數：ABS(x)，x的絕對值。
          CEIL(x)，返回不小於X 的最小整數值。
          FLOOR(x)，返回不大於X的最大整數值
          CRC32(x)，計算迴圈冗餘碼校正值。
          RAND()，返回0~1直接隨機浮點數。
          SIGN(x)，返回x的符號
          TRUNCATE(X,D)，返回X被捨去至小數點後D位的數字。

 

 

日期類型:
TIMESTAMP：
以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示TIMESTAMP值。支援的範圍為‘1970-01-01 00:00:00’到2037年。佔用4位元組

DATETIME：
以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示DATETIME值。支援的範圍為'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。佔用8位元組。

DATE：
用'YYYY-MM-DD'格式檢索和顯示DATE值。支援的範圍是'1000-01-01'到 '9999-12-31'。佔用4位元組。

 

 

日期類型處理函數
DATE_SUB/DATE_ADD：對時間進行加減。
CURDATE()：將當前日期按照‘YYYY-MM-DD’ 或YYYYMMDD 格式的值返回。
NOW()：返回當前日期和時間值，其格式為 ‘YYYY-MM-DD HH:MM:SS’ 或YYYYMMDDHHMMSS。
組合使用：mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
                             -> '1998-02-02'

 

字元類型
Char(M):
長度固定為建立表時聲明的長度M。長度可以為從0到255的任何值。當儲存CHAR值時，在它的右邊填充空格以達到指定的長度。當檢索到CHAR值時，尾部的空格被刪除。在儲存或檢索過程中不進行大小寫轉換。佔用空間(M*每字元位元組長度)，UTF8為3位元組。

VarChar(M):
列中的值為可變長字串。長度可以指定為0到65,535之間的值。VARCHAR的最大有效長度由最大行大小和使用的字元集確定。整體最大長度是65,532位元組。VARCHAR值儲存時不進行填充。當值儲存和檢索時尾部的空格仍保留，符合標準SQL。佔用空間(M*每字元位元組長度+長度記錄位)。

TINYTEXT:
真實字元+1個位元組,255 位元組

TEXT:
真實字元+2個位元組,65,535 位元組

MEDIUMTEXT:
真實字元+3個位元組,16,777,215 位元組

LONGTEXT:
真實字元+4個位元組,4,294,967,295 位元組

 

字元類型處理函數:
CHAR_LENGTH(str)：傳回值為字串str的長度，長度的單位為字元。
LENGTH(str)：傳回值為字串str 的長度，單位為位元組。
CONCAT(str1,str2,...)：返回結果為串連參數產生的字串。
CONCAT_WS(separator,str1,str2,...)：第一個參數是其它參數的分隔字元。分隔字元的位置放在要串連的兩個字串之間。
LEFT(str,len)：返回從字串str開始的len最左字元。
RIGHT(str,len)；從字串str 開始，返回最右len 字元。
SUBSTRING(str,pos,len)：從字串str返回一個長度同len字元相同的子字串，起始於位置 pos。
LOWER(str)：返回字串 str 變為小寫字母的字元。
UPPER(str)：返回字串str轉化為大寫字母的字元。

 

二進位類型:
BINARY(M):
M位元組,255個字元

VARBINARY(M):
M+1OR M+2 位元組;65,533 位元組

TINYBLOB:
真實位元組+1個位元組;255 位元組

BLOB:
真實位元組+2個位元組;65,535 位元組

MEDIUMBLOB:
真實位元組+3個位元組;16,777,215 位元組

LONGBLOB:
真實位元組+4個位元組;4,294,967,295 位元組

 

 

類型處理小技巧：
IP儲存：INET_ATON(expr)，將IP轉換為整數。
               INET_NTOA(expr)，將整數轉換為IP。
mysql> SELECT INET_ATON('209.207.224.40');
         -> 3520061480
mysql> SELECT INET_NTOA(3520061480);
        -> '209.207.224.40‘
判斷IP段：INET_ATON('209.207.224.1') <= IP AND IP <= INET_ATON('209.207.224.255');
VARCHAR儲存：(3*4+3)+1=16位，UTF8字元集15*3+1=46位元組
INT儲存：4位元組

 

 

 

-----end------