mysql資料類型

標籤：mysql資料類型、字元集

一般來說，一個頁內可以存放儘可能多的行，那麼資料庫的效能就越好。而且選擇錯誤的資料類型，會加大後期資料庫維護成本。

UNSIGNED屬性就是將數字類型無符號化。

help int;

select f1 - f2 from t1;-- mysql對unsigned數的操作，返回都是unsigned，除非調節SQL_MODE設定

盡量不要unsigned, 除非zerofill, 若int存不了, 就用bigint

-- ---------------------------------------------

zerofill  -- 一旦啟用ZEROFILL屬性，mysql為列自動添加UNSIGNED屬性。

zerofill只是在查詢時的格式化輸出（如果寬度小於設定的寬度，則自動填滿0）, 資料庫內部儲存的還是1而不是0001

select a,HEX(a) from t\G;

int(11) 若無zerofill, 這個11毫無作用，int整形是4位元組

alter talbe t1 modify f1 int(4) zerofill;

-- ---------------------------------------------

sql mode

sql mode的strict 模式為MySQL提供了很好的資料校正功能, 保證了資料的準確性，盡量不要預設（空）。

show variables like ‘%sql_mode%’;  -- sql_mode有global和session會話

select @@sql_mode;

set sql_mode = ‘‘;  -- sql_mode可以設定的選項有很多，一般最好設成strict 模式

set sql_mode = ‘traditional‘;

-- ---------------------------------------------

default預設值, 預設為null, 當沒有給某欄位顯式賦值時, 就用這個值

not null即值不能為null

-- ---------------------------------------------

auto_increment

一張表中最多一個, 沒有也可沒有

必須是索引(key), 一般為主鍵索引

select last_insert_id(); -- 取自動成長列的值

重設:

alter table user auto_increment = 1;

-- 還有一些其他參數可以設定步長以及初始值

-- ---------------------------------------------

整型：tinyint 1B 、smallint 2B 、mediumint 3B 、int 4B 、bigint 8B。

位類型：BIT

關於數位經典SQl編程問題：1、數字輔助表，2、連續範圍問題。

-- ---------------------------------------------

高精度類型：

decimal 和 numeric基本相同，最大位元是65。可應用於存錢。

salary dec(5, 1)

最多5位元, 其中一定1位小數, 整數位最多4位,  5為精度, 1為標度，會4舍5入

-- ---------------------------------------------

float  -- 表達小數

where score = 3.14; -- 有問題

float(6, 2)

double precision

-- ---------------------------------------------

字串型

國際化公司, 需要儲存的字元是多種多樣的, 因此要使用unicode編碼的字元集如utf8

unicode是一種編碼方式

mysql>show charset；

utf8是一種字元集, 是一種儲存格式

utf即unicode translation format, 即把unicode編碼的字元轉成某種格式來儲存

utf8字元集中, 一個漢字佔2 ~ 4個位元組, 絕大部分佔3個位元組, 極少數佔2個或4個位元組

gb2312漢字均佔2個位元組，gbk是gb2312的超集。

一般對於跨國性業務，最初字元集最好採用utf8，解決移植開發相容性問題。

-- ---------------------------------------------

char

show variables like ‘%char%‘;

char(8)存‘ab‘, 佔8B, insert ‘ab‘, 儲存時會在ab後補6個空格, select取出時會去掉空格

8個英文或中文字元

-- ---------------------------------------------

varchar(8)存的是‘ab‘, 佔3B -- 儲存時需要在前置長度列表加上實際儲存的字元，佔1~2位元組。

8個英文或中文字元

簡歷、微博內容, 文字量不是特別大就可用varchar而不用text，有些儲存引擎會將大的varchar類型字串自動轉化為text或blob類型。

select length(‘中國人‘);

一般用varchar類型因為char類型長度是固定分配的。

-- ---------------------------------------------

binary和varbinary儲存二進位的字串。binary（N）等N代表的是位元組數，而char（N）等N代表的是字元長度，另外char和varchar字元比較的是本身儲存的字元，忽略字元後的填充字元，而對binary和varbinary來說，按二進位值來比較。最後對於binary字串，其填充字元是0x00，char是0x20，0x00比較的是最小字元。

-- ---------------------------------------------

text、blob, 不能有預設值，建立索引時指定前置長度以及排序時只使用列的前max_sort_length個位元組

text 存大量文字如新聞內容, 不區分大小寫

blob(large binary object), 區分大小寫

-- ---------------------------------------------

enum(enumeration)和set類型都是集合類型,不同的是enum最多可枚舉65535個元素 適用user表

，一次只能選一個成員，而set類型最多枚舉64個元素。enum結合SQL_MODEstrict 模式約束，有用！

性別男女, 許可權有沒有, 是否管理員, 是否熱點新聞，

enum類型只能從成員當中選擇一個，而set 類型可以選擇多個，

enum用法：那麼對於多個值當中選取一個的話，可以選擇enum類型，比如，性別（男女）二選一

set  類型用法：比如個人愛好，可以選擇多個，那麼這個使用我們用set類型

sex enum(‘M‘, ‘F‘)

-- ---------------------------------------------

日期時間型：（mysql對日期和時間的設定非常寬鬆）

datetime  8B 、DATE 3B 、timestamp 4B 、year  1B 、 time 3B  

值用‘‘引起

datetime和timestamp顯示結果一樣，占位元組不同，以及實際儲存內容datetime可以到目前時間的毫秒數，另外在建表時timestamp類型可以設定一個預設值而datetime不行（必須對錶操作，即有改動），而且timestamp會自動更新目前時間。

datetime類型表示的時間範圍比timestamp的類型要大，因此，需要時間範圍比較大的選擇datetime類型比較合適，

timestamp類型的時間是根據時區來選擇的，如果需要顯示的時間與時區對應，那麼選擇timestamp類型。

mysql 5.6.4版本後支援秒的小數部分:type_name(fsp)。mysql類型不精確到微妙不過可以用microsecond函數提取

select now();

curdate() date(now());

curtime() time(now());

now、current_timestamp和sysdate都用於返回當前系統時間，不過前兩者返回的是執行SQL語句時的時間，而sysdate函數是返回執行當前函數時的時間。

時間加減函數有date_add和date_sub。date_format函數（防止誤使用）是按使用者需求格式化列印日期。

日期經典sql編程問題：1、生日問題，2、重疊問題，3、星期數問題。

WEEKDAY函數返傳回值是0~6,0為monday，DAYOFWEEK函數傳回值是1~7，1代表sunday。

-- ----------------------------------------------------------------

字元集

字元(character)是指人類語言中最小的表義符號, 例如‘a‘、‘b‘等

給定一系列字元, 對每個字元賦予一個數值

用數值來代表對應的字元, 這一數值就是字元的編碼(encoding)

例如, 我們給字元‘a‘賦予數值0, 給字元‘b‘賦予數值1

則0就是字元‘a‘的編碼, 1就是字元‘b‘的編碼

給定一系列字元並賦予對應的數值編碼後

所有這些字元和編碼對組成的集合就是字元集(character set)

例如, 給定字元集合為{‘a‘, ‘b‘}時, {‘a‘:0, ‘b‘:1}就是一個字元集

os有字元集, 軟體有字元集, 若軟體無字元集則使用os的字元集

校對規則(collation), 也叫字元序, 是指在同一字元集內字元之間的比較規則

確定字元序後, 才能在一個字元集上定義什麼是等價的字元, 以及字元之間的大小關係

區分大小寫是一種順序, 不區分大小寫是一種順序

一個字元集可以對應多種字元序, 其中有一個是預設字元序

每個字元序唯一對應一種字元集

             _ci  不區分大小寫  -- 應用程式有這需求，不過建唯一索引會出錯

定序   ：_cs  區分大小寫

             _bin 按編碼值比較

對於國際化公司, 需要儲存的字元是多種多樣的, 因此要使用unicode編碼的字元集如utf8

unicode是一種編碼方式, 而utf8是一種儲存方式

utf是unicode translation format, 即把unicode轉成某種格式的意思

查看所有可用的字元集

show charset;

int、decimal不能指字元集

char、varchar、text字串資料型別才可指

校對規則:  -- 沒指定校對規則, 則使用該字元集預設的校對規則

show collation;

字元集與校對規則有4個層級的設定

server

db

table

column

[mysqld]

character_set_server=utf8   -- mysql5.5開始移除了default-character-set參數，取而代之為default-character-server。

==

create database ds charset=gbk;

alter database ds charset=utf8; -- 只對新建立的表有效

alter table emp charset = utf8;

用戶端字元集:

default-character-set=utf8; #相當於set names utf8; --set names可以更改當前會話串連的字元集、當前會話的用戶端的字元集以及當前會話返回集的字元集,mysql>status; 可看！

[mysql]

寫入資料前, 查看資料前先設定用戶端字元集, 即set names utf8;

本文出自 “8430296” 部落格，請務必保留此出處http://8440296.blog.51cto.com/8430296/1584573

mysql資料類型