mysql数据库 数据类型自动编号选哪个_MySQL表类型、选择合适数据类型、字符集...
MySQL學習筆記(4)
表類型(存儲引擎)的選擇
插件式存儲引擎是MySQL最重要特性之一，5.5之前默認引擎為MyISAM，之后為InnoDB，如需修改默認存儲引擎，可在參數文件中設置default_storage_engine。
查看默認引擎:show variables like %storage%
查看當前數據庫支持的存儲引擎:show engines\G(\G的花式用)
需要查看某一種存儲引擎的詳細信息，如查看MyISAM：? MyISAM
創建表時指定存儲引擎：
create table ai(
i bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(i)
)ENGINE=myisam default charset=utf8
修改表的存儲引擎：alter table tablename engine=enginename
常用存儲引擎對比
特點
MyISAM
InnoDB
MEMORY
MERGE
NDB
存儲限制
有
64T
有
沒有
有
事物安全
支持
鎖機制
表鎖
行鎖
表鎖
表鎖
行鎖
B樹索引
支持
支持
支持
支持
支持
哈希索引
支持
支持
全文索引
支持
集群索引
支持
數據緩存
支持
支持
支持
索引緩存
支持
支持
支持
支持
支持
數據可壓縮
支持
空間使用
低
高
N/A
低
低
內存使用
低
高
中等
低
高
批量插入的速度
高
低
高
高
高
支持外鍵
支持
MyISAM
5.5之前的默認引擎，不支持事務和外鍵，訪問速度快。每個MyISAM在磁盤上存儲成3個文件，文件名和表名字相同，擴展名為：
- .frm(表定義)
- .MYD(MYData,數據)
- .MYI(MYIndex,索引)
數據文件和索引文件可放在不同目錄，平均分布IO，獲取更快速度。指定索引和數據文件路徑需要在創建表時通過DATA DIRECTORY和INDEX DIRECTORY指定，文件路徑為絕對路徑且具有可訪問權限。
MyISAM類型的表可用check table來檢查表的健康，并用repair table語句修復一個損壞的MyISAM表。(預知詳情，我現在也不知道，后面文章中會詳解)
MyISAM表支持3中不同存儲格式：
靜態(固定長度)表
動態表
壓縮表
靜態表為默認存儲格式，字段固定長度，優點存儲快，易緩存；缺點占用空間比動態表多。存儲時按列寬度補足空格，但訪問之前會去掉。動態表包含變長，優點是占用空間相對較少，缺點是頻繁更新會產生碎片，需定期OPTIMIZE TABLE或者myisamchk -r改善性能。壓縮表由myisampack工具創建，占磁盤空間小。
InnoDB
自動增長列
InnoDB的自動增長列可以手工插入，但是插入的值如為空或者0，則實際將是自動增長的值。可以通過alter table tablename auto_increment=n強制設置自動增長列的初始值，默認從1開始，重啟失效。可使用LAST_INSERT_ID()查詢最后插入記錄自增使用的值。一次插入多條是第一條記錄使用的自動增長值(有點雞肋的功能)。
對InnoDB，自增列必須是索引，也必須是主鍵。
外鍵約束
MySQL在創建外鍵時，要求父表必須有對應的索引，字表在創建外鍵的時候也會自動創建對應的索引。并且在父表對應的主鍵或索引禁止刪除。暫時關閉外鍵約束的命令：set foreign_key_checks=0,等于1則恢復。查詢外鍵信息：show craete table或者show table status like 'tablename'\G
存儲方式
InnoDB存儲和索引表有兩種方式，一種為使用共享空間存儲，一種是使用多表空間存儲。要使用多表空間存儲方式，需要設置參數innodb_file_per_table。
MEMORY
MEMORY存儲引擎用存在于內存中的內容來創建表。每個MEMORY表只實際對應一個.frm磁盤文件。默認使用HASH索引，服務關閉，表中數據丟失。
MERGE
MERGE存儲引擎是一組MyISAM表的組合，表結構完全相同，MERGE表本身并沒有數據，對MERGE類型表的操作實際會映射為對內部MyISAM表的操作，對MERGE插入操作，通過INSERT_METHOD自定義插入的表，該值有三個FIRST或者LAST或不定義/NO(表示不能執行插入操作)。可對MERGE進行DROP操作，但是只刪除MERGE定義，對內部表沒有影響。MERGE表在磁盤上保留兩個文件，一個.frm文件存儲表定義，另一個.MRG文件包含組合表的信息。可以通過修改.MRG文件來修改MERGE表，修改后通過FLUSH TABLES刷新。以下為示例：
create table payment_2006(
country_id smallint,
payment_date datetime,
amount decimal(15,2),
key idx_fk_country_id(country_id)
)engine=myisam
create table payment_2007(
country_id smallint,
payment_date datetime,
amount decimal(15,2),
key idx_fk_country_id(country_id)
)engine=myisam
create table payment_all(
country_id smallint,
payment_date datetime,
amount decimal(15,2),
INDEX(country_id)
)engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST
以上向payment_2006和payment_2007中插入數據后都會進入payment_all,該表是以上兩表記錄合并后的結果集。
如何選擇合適存儲引擎
MyISAM
以讀和插入操作為主，只有很少跟新和刪除，并對事務的完整性和并發性要求不高。
InnoDB
用于事務處理應用程序，支持外鍵。對事務和并發有要求，并且除了插入和查詢還有不少更新和刪除。
MEMORY
將所有數據放在RAM中，因此很快，但對大小有限制，用于更新不太頻繁的小表。
MERGE
將MyISAM表組合，作為一個對象引用它們，可以突破對單個MyISAM表大小的限制。
數據類型的選擇
CHAR和VARCHAR
總的來說，CHAR是定長，且對空格的處理和VARCAHR不同，CHAR保留空格，VARCAHR不保留，讀取時候都去掉空格。MyISAM存儲引擎建議使用char；MEMORY存儲引擎都會轉為CAHR類型處理；InnoDB建議用VARCHAR。
TEXT和BLOB
BLOB和TEXT值會引起一些性能問題，特別是執行大量的刪除操作時。刪除會在數據表中留下很大“空洞”，建議定期使用OPTIMIZE TABLE對這類表進行碎片整理。如下示例：
create table t(id varcahr(100),context text);
insert into t values(1,repeat('haha',100));
insert into t values(2,repeat('haha',100));
insert into t values(3,repeat('haha',100));
insert into t select * from t;
====================
此時查看數據表物理磁盤大小：
dh -sh t.*
16K t.frm
155M t.MYD
8.0K t.MYI
====================
delete from t where id=1;
此時再查看發現所占用磁盤大小不變。由于大量碎片夾雜其間。執行OPTIMIZE：
OPTIMIZE table t;
====================
du -sh t.*
16K t.frm
104M t.MYD
8.0K t.MYI
可以使用合成索引來提高大文本字段的查詢性能。簡單說就是根據大文本字段內容建立一個散列值，并且把這個值存在單獨數據列中，通過索引散列找到數據行。這只能用于精確查詢。可以用MD5()或者其他加密算法生成散列值。
在不必要時避免檢索大型BLOB或TEXT值
將BLOB或TEXT列分離到單獨的表中
浮點數和定點數
浮點一般用于表示包含小數部分的數值，當一個字段被定義為浮點類型后，插入數據的精度超過該列定義的實際精度，則插入值會被四舍五入到實際的精度值。定點數則以字符串形式存放，插入數值精度大于實際精度，則會四舍五入。
浮點數存在誤差問題。
對貨幣精度敏感的數據，用定點存儲。
編程中避免浮點數比較
浮點數中特殊值的處理
日期類型選擇
根據實際需要選擇能滿足應用的最小存儲的日期類型
記錄年份比較久遠選擇DATETIME
記錄的日期需要不同時區的用戶使用，最好使用TIMESTAMP
選擇字符集
總之使用UTF8就沒錯
查看所有可用的字符集:shwo character set
查看字符集默認校對規則：desc information_schema.character_sets
MySQL的字符集包括字符集(CHARACTER)和校對規則(COLLATION)兩個概念。字符集用來定義MySQL存儲字符串的方式，校對規則用來定義比較字符串的方式。每個字符集至少對應一個校對規則，可以用show collation like 'utf8%'或者查看information_schema.COLLATIONS來查看。
校對規則命名約定：以其相關的字符集名開始，通常包含一個語言名，并以_ci(大小寫不敏感)、_cs(大小寫敏感)或_bin(比較字符編碼的值)
MySQL字符集的設置
mysql的字符集和校對規則有4個級別的默認設置：服務器、數據庫、表和字段。分別在不同地方設置，作用也不同。
服務器字符集和校對規則
在my.cnf中設置
[mysqld]
character-set-server=utf8
啟動選項中指定
mysqld --character-set-server=utf8
在編譯時指定
cmake . -DEFAULT_CHARSET=utf8
默認使用latin1作為服務器字符集，以上沒有設置校對規則，也就是使用該字符集的默認校對規則。
查詢當前服務器字符集和校對規則shwo variables like '%_server'
數據庫(表)字符集和校對規則
可以在創建數據庫是指定，也可以通過alter database(table)修改。設置數據庫字符集規則：
都設置了，使用設置的
沒設置校對規則，使用指定字符集的默認校對規則
沒設置字符集，使用于改校對規則關聯的字符集
都沒有設置，使用服務器字符集和校對規則
查看當前數據庫字符集和校對規則：show variables like '%_database'
查看數據表的字符集和校對規則：show create table tablename\G
列字符集和校對規則
可以定義，但很少用，算是mysql提供的一個很靈活的設置方法。沒設置使用表的字符集和校對規則
連接字符集和校對規則
對實際的應用訪問來說，還存在客戶端和服務器之間交互的字符集和校對規則。mysql提供了3個不同參數：character_set_client、character_set_connection和character_set_results分別代表客戶端、連接和返回結果的字符集。通常應該保證這三個字符集相同。
同時修改以上三種字符集：set names ***
以上方法只能在當前連接生效，每次新的連接后都要設置
在配置文件中修改：
[mysqld]
default-character-set=utf8
可以通過[_charset_name]'string'[collate collation_name]命令強制字符串的字符集和校對規則：
select _gbk '字符串'
select _utf8 '字符串'
字符集的修改步驟
對已存儲了數據的數據庫，需要修改字符集不能通過直接執行alter database charcter set **或者alter table tablename character set **命令進行，這兩個命令都沒有更新已有記錄的字符集。已有記錄字符集調整需要先將數據導出，調整后再導入。以下模擬將latin1字符集的數據庫修改成gbk字符集數據庫的過程:
導出表結果:
mysqldump -uroot -p --default-character-set=gbk -d databasename > createtab.sql
手工修改create.sql中表結構定義中的字符集為新的字符集
確保記錄不再更新，導出所有記錄
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename>data.sql
打開data.sql，將set names latin1修改成set names dbk
使用新的字符集創建新的數據庫
create database databasebname default charset gbk
創建表，執行createtab.sql
mysql -uroot -p databasename < createtab.sql
導入數據，執行data.sql
mysql -uroot -p databasename < data.sql
選擇目標字符集時為避免丟失數據，最好選擇當前字符集的超集
總結
以上是生活随笔為你收集整理的mysql数据库 数据类型自动编号选哪个_MySQL表类型、选择合适数据类型、字符集...的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： html5中api有什么,HTML5中的
- 下一篇： mysql数据库扫描_使用nmap对my