共計 6477 個字符，預計需要花費 17 分鐘才能閱讀完成。
這篇文章主要介紹“mysql 中表的知識點總結”，在日常操作中，相信很多人在 mysql 中表的知識點總結問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”mysql 中表的知識點總結”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！
1.
表
創建表的原則：
l
禁止使用中文做字段名；
l
禁止使用字符型做主鍵；
l
禁止無主鍵或唯一索引；
1.1.
整型
int、tinyint 是使用最多的整型類型。
主鍵選擇：
為什么要選擇 ID 做主鍵：主鍵字段要選擇不經常修改的、盡量與業務無關、無具體含義的。因為 InnoDB 是索引組織表，需要保證索引結構不經常防撞，避免造成性能的消耗。
int(4) 和 int(10) 都是占用 4 字節空閑，區別就是數據庫中表示的數據位數，一個 4 位一個 10 位。有些在數字前自動用 0 補位。
1.2.
浮點型
常用 decimal 存儲金錢字段，但在運算過程中會轉成浮點運算，且會出現四舍五入的情況，建議使用 int 類型。
decimal(M,D);
D 是小數部分位數，超過小數部分四舍五入截斷，不足補足；
M 是整數部分加小數部分的總長度，即插入整數部分不能超過 M - D 位，否則插入失敗。
1.3.
時間類型
datetime:5.6 前占 8 個字節，5.6 后占 5 個字節，可用范圍比 timestamp 大，物理存儲上僅比 timestamp 多一個字節；
可以用 int 存儲時間，通過兩個函數轉換:unix_timestamp 和 from_unixtime。
[mysql] select unix_timestamp(2018-11-06 16:42:00
+—————————————+
| unix_timestamp(2018-11-06 16:42:00) |
+—————————————+
| 1541493720 |
+—————————————+
[mysql] select from_unixtime(1541493720);
+—————————+
| from_unixtime(1541493720) |
+—————————+
| 2018-11-06 16:42:00 |
+—————————+
從 5.6 開始，datetime 和 timestamp 都支持自動更新為當前時間。
1.4.
字符串類型
text 和 blob 這種大數據類型建議不要和業務表放一起。
char 和 varchar 區別
char：用于定長字符串，范圍 0~255，不夠空格補全存庫；超過截斷；
varchar: 變長，范圍 0~65535，不夠不補；超過截斷；可節約空間提高存儲效率。
varchar 多用一到兩個字節記錄長度，數據位占用字節小于 255 用 1 字節記錄長度；超過 255 則用 2 字節；還有一位用來記錄是否為 null 值；
示例：varchar(100)
UTF8 字符集：存儲空間 100*3+1=301 字節；
GBK 字符集：存儲空間 100*2+1=201 字節；
mysql 每行最大字節數 65535，不同字符集下字符最大長度；
使用 UTF8，每個字符最多占 3 個字節，最大長度不能超過（65535-1-2）/3=21844
使用 GBK，每個字符 2 字節，最大長度不超過 (65535-1-2)/2=36766
存儲 IP, 推薦用 int 存儲，使用 inet_aton 和 inet_ntoa 兩個參數；
[mysql] select inet_aton(10.98.156.210
+—————————-+
| inet_aton(10.98.156.210) |
+—————————-+
| 174234834 |
+—————————-+
[mysql] select inet_ntoa(174234834);
+———————-+
| inet_ntoa(174234834) |
+———————-+
| 10.98.156.210 |
+———————-+
1.5.
字符集
mysql 數據庫字符集包括字符集（character）和校對規則（collation）兩個概念。
字符集：定義 mysql 數據庫字符串的存儲方式；
校對規則：定義比較字符串的方式。
常用字符集：
l
GBK：每個漢字兩個字節。
l
Latin1：停用。5.1 前默認，1 漢字或字母占 1 字節。
l
UTF8：每個漢字 3 個字節。
l
UTF8mb4: 是 utf8 的超集，每個漢字 4 個字節。5.7 建議使用。
數據庫配置文件中相關參數
[mysql] show variables like %character%
+————————–+———————————-+
| Variable_name | Value |
+————————–+———————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+————————–+———————————-+
避免漢字亂碼，做到三線統一；
連接端的字符集必須是 UTF8;
操作系統字符集必須是 UTF8,
mysql 數據庫字符集必須是 UTF8；
[mysql] \s
————–
mysql Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 9
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile:
Using delimiter: ;
Server version: 5.7.24-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 3 hours 6 min 28 sec
Threads: 1 Questions: 299 Slow queries: 3 Opens: 372 Flush tables: 1 Open tables: 144 Queries per second avg: 0.026
————–
數據庫臨時修改字符集：命令行執行 set names 字符集名稱;
如：set names utf8
1.6.
表碎片產生的原因
delete 操作產生數據碎片，碎片占用磁盤空間且讀取效率低。
1.7.
碎片計算方法及整理過程
查看表統計信息：
[mysql] show table status like t
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 3
Create_time: 2018-11-06 10:20:10
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
碎片大小計算
碎片大小 = 數據總大小 - 實際表空間文件大小
數據總大小 =data_length+index_length
實際表空間文件大小 =rows*avg_row_length
碎片大小 MB=（數據總大小 - 實際表空間文件大小）/1024/1024
清除碎片的兩種方法：
alter table table_name engine=innodb；會給表加寫鎖，時間長。
備份原表數據，刪掉，重新導入新表中；
在線整理表結構的工具 pt-online-schema-charge；
Mysql 5.7 后已支持在線 online ddl 了。
1.8.
表統計信息
統計每個庫大小
SELECT
table_schema,
sum(data_length) / 1024 / 1024 / 1024 AS data_length,
sum(index_length) / 1024 / 1024 / 1024 AS index_length,
sum(data_length + index_length) / 1024 / 1024 / 1024 AS sum_data_index
FROM
information_schema. TABLES
WHERE
TABLE_SCHEMA != information_schema
AND TABLE_SCHEMA != mysql
GROUP BY
table_schema;
統計庫中每個表大小
SELECT
TABLE_NAME,
SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE
FROM
information_schema.`TABLES`
WHERE
TABLE_SCHEMA = mysql
GROUP BY
TABLE_NAME;
統計所有數據的大小
SELECT
SUM(DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE
FROM
information_schema.`TABLES`;
1.9.
統計信息的收集方法
l
遍歷 information_schema_tables，收集 su 表的統計信息；
[mysql] select * from information_schema.tables where table_name= t \G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: mysql
TABLE_NAME: t
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: 3
CREATE_TIME: 2018-11-06 10:20:10
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
l
重啟 mysql 實例
l
show table status like‘%table_name%’;
[mysql] show table status like t
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 3
Create_time: 2018-11-06 10:20:10
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
1.10.
MySQL 庫表常用命令總結
登陸同時修改命令提示符: 主機 - 用戶 - 數據庫 - 日期 mysql -uroot -p –prompt= \h-\u-\d-\D
退出：exit quit \q
命令行結束符號：；或 \g
\c 取消當前命令執行，就不用刪除了；
MySQL 注釋：# 或 —
\s 實例信息；
show database; 顯示實例下所有數據庫；
show schemas; 顯示實例下所有數據庫；
show warings; 查看警告
use database; 選擇數據庫；
show full processlist; 查看數據庫當前連接情況；
select user(); 得到登陸用戶
select version(); 得到版本信息；
select now(); 得到當前日期時間；
seleect database(); 得到當前打開數據庫；
create database db_name；創建數據據庫；
create databse if not exists test1; 檢測數據庫不存在則創建
create databse if not exists test1 default character set utf8 創建時指定編碼方式
show create database dbname; 查看數據庫信息
alter databse dbname default character set gbk 修改指定數據庫的編碼方式
drop database db_name; 刪除數據庫；
show tables; 查看庫下所有表；
show create table tab_name \G; 查看建表語句；
desc tab_name; 查看表結構；
show table status; 獲取表基礎信息；
show index from tab_name; 查看當前表下索引情況；
create table tab_name: 創建表；
drop table tab_name; 刪除表包括結構；
select * from tab_name;
delete from tab_name where ; 或 truncate table tab_name;
insert into tab_name (字段列表) values（對應字段值）
update tab_name set : 字段名 = 某值（where）;
到此，關于“mysql 中表的知識點總結”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！
