MySQL數(shù)據(jù)類型與表操作全指南(?從基礎(chǔ)到高級實踐)

更新時間：2025年08月08日 16:18:16 作者：搬磚的碼農(nóng)

本文詳解MySQL數(shù)據(jù)類型分類（數(shù)值、日期/時間、字符串）及表操作（創(chuàng)建、修改、維護）,涵蓋優(yōu)化技巧如數(shù)據(jù)類型選擇、備份、分區(qū),強調(diào)規(guī)范設(shè)計與實際應(yīng)用結(jié)合,本文結(jié)合實例代碼給大家介紹的非常詳細,感興趣的朋友一起看看吧

MySQL數(shù)據(jù)類型詳解

MySQL支持多種數(shù)據(jù)類型，主要分為三類：數(shù)值類型、日期/時間類型和字符串類型。

數(shù)值類型

數(shù)值類型用于存儲數(shù)字，包括整數(shù)和浮點數(shù)：

類型	大小(字節(jié))	范圍（有符號）	說明
TINYINT	1	-128 到 127	小整數(shù)值
INT	4	-2147483648 到 2147483647	標準整數(shù)
BIGINT	8	±9.22e18	大整數(shù)
FLOAT	4	-3.402823466E+38 到 3.402823466E+38	單精度浮點數(shù)
DOUBLE	8	±1.7976931348623157E+308	雙精度浮點數(shù)
DECIMAL(M,D)	變長	取決于M和D	精確小數(shù)，M總位數(shù)，D小數(shù)位

示例：

CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2), -- 總10位，含2位小數(shù)
    quantity SMALLINT UNSIGNED -- 無符號小整數(shù)
);

日期時間類型

日期和時間類型用于存儲時間信息：

類型	格式	范圍	說明
DATE	YYYY-MM-DD	1000-01-01 到 9999-12-31	日期值
TIME	HH:MM:SS	-838:59:59 到 838:59:59	時間值
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 到 9999-12-31 23:59:59	混合日期時間
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 到 2038-01-19 03:14:07	時間戳，自動更新
YEAR	YYYY	1901 到 2155	年份值

字符串類型

字符串類型用于存儲文本和二進制數(shù)據(jù)：

類型	最大長度	說明
CHAR(n)	255字符	定長字符串，空格填充
VARCHAR(n)	65,535字符	變長字符串，節(jié)省空間
TEXT	65,535字符	長文本數(shù)據(jù)
BLOB	65,535字節(jié)	二進制大對象
ENUM	65,535項	枚舉類型，值從預(yù)定義列表中選擇
SET	64個成員	集合類型，允許選擇多個預(yù)定義值

示例：

CREATE TABLE users (
    username VARCHAR(50) NOT NULL,
    gender ENUM('Male','Female','Other'),
    interests SET('Music','Sports','Reading')
);

表操作全解析

創(chuàng)建表

基本語法：

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    PRIMARY KEY (one_or_more_columns)
);

完整示例：

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    birth_date DATE,
    hire_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    salary DECIMAL(10,2) CHECK (salary > 0),
    PRIMARY KEY (emp_id),
    UNIQUE (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

修改表結(jié)構(gòu)

添加列

ALTER TABLE employees
ADD COLUMN email VARCHAR(100) AFTER last_name;

修改列

-- 修改數(shù)據(jù)類型
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12,2);
-- 重命名列
ALTER TABLE employees
CHANGE COLUMN birth_date date_of_birth DATE;

刪除列

ALTER TABLE employees
DROP COLUMN hire_date;

約束管理

添加主鍵

ALTER TABLE orders
ADD PRIMARY KEY (order_id);

添加外鍵

ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES orders(order_id)
ON DELETE CASCADE;

添加唯一約束

ALTER TABLE users
ADD UNIQUE (email);

表維護操作

重命名表

RENAME TABLE old_name TO new_name;
-- 或
ALTER TABLE old_name RENAME TO new_name;

截斷表

TRUNCATE TABLE log_entries; -- 快速刪除所有數(shù)據(jù)

刪除表

DROP TABLE IF EXISTS temp_data;

表優(yōu)化技巧

選擇合適的數(shù)據(jù)類型
- 用INT代替VARCHAR存儲數(shù)字
- 用DATE代替DATETIME如果不需要時間部分
- 用ENUM代替VARCHAR存儲固定選項
規(guī)范命名約定

CREATE TABLE customer_orders (  -- 使用蛇形命名法
    order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id)
);

使用注釋增強可讀性

CREATE TABLE payments (
    payment_id INT COMMENT '主鍵ID',
    amount DECIMAL(10,2) COMMENT '支付金額',
    payment_method ENUM('Credit','Paypal','Bank') 
        COMMENT '支付方式'
) COMMENT='支付信息表';

分區(qū)大表優(yōu)化查詢

CREATE TABLE sensor_data (
    id INT AUTO_INCREMENT,
    sensor_id INT,
    reading_time TIMESTAMP,
    value FLOAT,
    PRIMARY KEY (id, reading_time)
) PARTITION BY RANGE (YEAR(reading_time)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

最佳實踐與注意事項

備份優(yōu)先原則 執(zhí)行結(jié)構(gòu)變更前務(wù)必備份：

mysqldump -u root -p database_name > backup.sql

外鍵約束影響
- ON DELETE CASCADE：刪除主表記錄時自動刪除從表相關(guān)記錄
- ON DELETE SET NULL：將外鍵設(shè)為NULL
- 謹慎使用CASCADE避免誤刪連鎖反應(yīng)
字符集選擇
- 推薦utf8mb4支持所有Unicode字符（包括emoji）
- 校對規(guī)則：utf8mb4_unicode_ci（大小寫不敏感）
存儲引擎選擇

SHOW ENGINES; -- 查看支持的引擎

InnoDB：支持事務(wù)、行級鎖（默認）
MyISAM：全文索引，但不支持事務(wù)
Memory：數(shù)據(jù)存儲在內(nèi)存中
性能優(yōu)化
- 避免過度使用ENUM（修改值需重建表）
- TEXT/BLOB列單獨存到副表
- 定期分析表優(yōu)化存儲：

ANALYZE TABLE orders;
OPTIMIZE TABLE log_data;

實戰(zhàn)案例：電商系統(tǒng)表設(shè)計

-- 商品表
CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) UNSIGNED NOT NULL,
    stock INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB;
-- 訂單表
CREATE TABLE orders (
    order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status ENUM('Pending','Paid','Shipped','Completed') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE RESTRICT
) PARTITION BY HASH(order_id) PARTITIONS 4;
-- 訂單明細表
CREATE TABLE order_details (
    detail_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_product
        FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
);

常見問題解決方案

問題1：如何修改AUTO_INCREMENT起始值？

ALTER TABLE products AUTO_INCREMENT = 1000;

問題2：誤刪表如何恢復(fù)？

使用備份文件恢復(fù)
若無備份，嘗試從binlog恢復(fù)：

mysqlbinlog --start-datetime="2023-01-01 00:00:00" binlog.000001 | mysql -u root -p

問題3：大表添加列卡頓 使用pt-online-schema-change工具在線修改：

pt-online-schema-change --alter "ADD COLUMN new_col INT" D=database,t=table --execute

問題4：存儲引擎轉(zhuǎn)換

ALTER TABLE orders ENGINE = InnoDB; -- 轉(zhuǎn)換為InnoDB

進階技巧

生成列（Generated Columns）

CREATE TABLE invoices (
    subtotal DECIMAL(10,2),
    tax_rate DECIMAL(5,4),
    tax_amount DECIMAL(10,2) AS (subtotal * tax_rate) STORED,
    total DECIMAL(10,2) AS (subtotal + tax_amount) STORED
);

JSON數(shù)據(jù)類型操作

CREATE TABLE product_specs (
    product_id INT PRIMARY KEY,
    specs JSON
);
INSERT INTO product_specs VALUES (1, '{"color": "red", "weight": 500}');
SELECT specs->>"$.color" FROM product_specs;

表空間管理

-- 創(chuàng)建獨立表空間
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;
CREATE TABLE large_table (
    id INT PRIMARY KEY
) TABLESPACE ts1;

不可見列（MySQL 8.0+）

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    balance DECIMAL(10,2) INVISIBLE
);
INSERT INTO accounts (id) VALUES (1); -- 必須顯式指定可見列
SELECT * FROM accounts; -- 不顯示balance列
SELECT id, balance FROM accounts; -- 顯式查詢

通過深入理解MySQL數(shù)據(jù)類型和表操作，可以設(shè)計出高效可靠的數(shù)據(jù)庫結(jié)構(gòu)。實際應(yīng)用中需結(jié)合業(yè)務(wù)場景選擇合適的數(shù)據(jù)類型，遵循數(shù)據(jù)庫設(shè)計規(guī)范，并定期進行表結(jié)構(gòu)優(yōu)化維護。

到此這篇關(guān)于MySQL數(shù)據(jù)類型從基礎(chǔ)到高級實踐與表操作全指南的文章就介紹到這了,更多相關(guān)mysql數(shù)據(jù)類型內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: