前言：

在上一篇 MySQL 基礎(chǔ)入門中，我們了解了數(shù)據(jù)庫(kù)的基本概念和簡(jiǎn)單操作。而在實(shí)際開發(fā)中，數(shù)據(jù)庫(kù)和表的創(chuàng)建、修改、備份、刪除等操作是日常高頻需求，掌握這些精準(zhǔn)操作能避免數(shù)據(jù)丟失、提升開發(fā)效率。本文將基于 MySQL 實(shí)戰(zhàn)場(chǎng)景，詳細(xì)拆解庫(kù)與表的完整操作流程，包括字符集選擇、表結(jié)構(gòu)設(shè)計(jì)、備份恢復(fù)等核心知識(shí)點(diǎn)，帶你從 “會(huì)用” 進(jìn)階到 “活用” MySQL。

一. 數(shù)據(jù)庫(kù)（庫(kù)）的核心操作

數(shù)據(jù)庫(kù)是表的容器，合理的庫(kù)操作是數(shù)據(jù)管理的基礎(chǔ)。下面涵蓋庫(kù)的創(chuàng)建、查詢、修改、刪除、備份恢復(fù)等關(guān)鍵操作，同時(shí)詳解字符集和校驗(yàn)規(guī)則的影響。

1.1 創(chuàng)建數(shù)據(jù)庫(kù)：指定字符集與校驗(yàn)規(guī)則

創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，不僅要定義庫(kù)名，還需根據(jù)業(yè)務(wù)場(chǎng)景指定字符集（如支持中文的utf8）和校驗(yàn)規(guī)則（如是否區(qū)分大小寫），避免后續(xù)出現(xiàn)亂碼或查詢異常。

1.1.1 語(yǔ)法格式

CREATE DATABASE [IF NOT EXISTS] db_name 
[DEFAULT] CHARACTER SET charset_name 
[DEFAULT] COLLATE collation_name;

• IF NOT EXISTS：避免重復(fù)創(chuàng)建數(shù)據(jù)庫(kù)報(bào)錯(cuò)（可以不加但是這里推薦加）；
• CHARACTER SET：指定數(shù)據(jù)庫(kù)字符集（默認(rèn)utf8）；
• COLLATE：指定字符集的校驗(yàn)規(guī)則（默認(rèn)utf8_general_ci）。

1.1.2 實(shí)戰(zhàn)案例

-- 1. 創(chuàng)建默認(rèn)字符集的數(shù)據(jù)庫(kù)db1
CREATE DATABASE IF NOT EXISTS db1;
-- 2. 創(chuàng)建指定utf8字符集的數(shù)據(jù)庫(kù)db2
CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET utf8;
-- 3. 創(chuàng)建指定字符集和校驗(yàn)規(guī)則的數(shù)據(jù)庫(kù)db3
CREATE DATABASE IF NOT EXISTS db3 
CHARACTER SET utf8 
COLLATE utf8_general_ci;

1.2 字符集與校驗(yàn)規(guī)則：影響查詢和排序

字符集決定了數(shù)據(jù)的存儲(chǔ)編碼（如是否支持中文），校驗(yàn)規(guī)則則影響字符串的比較和排序（如是否區(qū)分大小寫），這是容易被忽略但關(guān)鍵的細(xì)節(jié)。

1.2.1 查看系統(tǒng)默認(rèn)配置

-- 查看默認(rèn)字符集
show variables like 'character_set_database';
-- 查看默認(rèn)校驗(yàn)規(guī)則
show variables like 'collation_database';

1.2.2 查看支持的字符集和校驗(yàn)規(guī)則

-- 查看所有支持的字符集
show charset;
-- 查看所有支持的校驗(yàn)規(guī)則
show collation;

1.2.3 校驗(yàn)規(guī)則的實(shí)際影響

以 “是否區(qū)分大小寫” 為例，對(duì)比兩種常用校驗(yàn)規(guī)則：

• utf8_general_ci：不區(qū)分大小寫（ci=case insensitive）；
• utf8_bin：區(qū)分大小寫（bin=binary，按二進(jìn)制比較）。

案例演示：

-- 1. 創(chuàng)建不區(qū)分大小寫的數(shù)據(jù)庫(kù)test1
CREATE DATABASE test1 COLLATE utf8_general_ci;
USE test1;
CREATE TABLE person(name varchar(20));
INSERT INTO person VALUES('a'),('A'),('b'),('B');
-- 查詢name='a'：返回'a'和'A'（不區(qū)分大小寫）
SELECT * FROM person WHERE name='a';
-- 排序：按字母順序排序（不區(qū)分大小寫）
SELECT * FROM person ORDER BY name;

-- 2. 創(chuàng)建區(qū)分大小寫的數(shù)據(jù)庫(kù)test2
CREATE DATABASE test2 COLLATE utf8_bin;
USE test2;
CREATE TABLE person(name varchar(20));
INSERT INTO person VALUES('a'),('A'),('b'),('B');
-- 查詢name='a'：僅返回'a'（區(qū)分大小寫）
SELECT * FROM person WHERE name='a';
-- 排序：按二進(jìn)制ASCII碼排序（大寫在前，小寫在后）
SELECT * FROM person ORDER BY name;

1.3 操縱數(shù)據(jù)庫(kù)：查詢、修改、刪除

1.3.1 查看所有數(shù)據(jù)庫(kù)

show databases;

1.3.2 查看數(shù)據(jù)庫(kù)創(chuàng)建語(yǔ)句

驗(yàn)證數(shù)據(jù)庫(kù)的字符集、校驗(yàn)規(guī)則等配置：

show create database db3;

輸出樣例：

+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci */ |
+----------+----------------------------------------------------------------+

• 反引號(hào) `：防止庫(kù)名與關(guān)鍵字沖突；
• /*!40100 ... */：條件執(zhí)行，MySQL 版本≥4.0.10 時(shí)生效。

1.3.3 修改數(shù)據(jù)庫(kù)（僅字符集和校驗(yàn)規(guī)則）

數(shù)據(jù)庫(kù)創(chuàng)建后，僅支持修改字符集和校驗(yàn)規(guī)則，不支持修改庫(kù)名（需通過(guò)備份恢復(fù)間接修改）：

-- 將db3的字符集改為gbk
ALTER DATABASE db3 CHARACTER SET gbk;

1.3.4 刪除數(shù)據(jù)庫(kù)（謹(jǐn)慎操作?。?/h4>

刪除數(shù)據(jù)庫(kù)會(huì)級(jí)聯(lián)刪除所有表和數(shù)據(jù)，且無(wú)法恢復(fù)：

DROP DATABASE IF EXISTS db3;

1.4 數(shù)據(jù)庫(kù)備份與恢復(fù)：避免數(shù)據(jù)丟失

備份恢復(fù)是數(shù)據(jù)庫(kù)運(yùn)維的核心技能，支持全庫(kù)備份、單表備份、多庫(kù)備份。

1.4.1 備份（退出 MySQL 客戶端執(zhí)行）

• 語(yǔ)法：

mysqldump -P端口 -u用戶名 -p密碼 -B 數(shù)據(jù)庫(kù)名 > 備份文件路徑

• 補(bǔ)充說(shuō)明：
  • 備份單表：mysqldump -uroot -p 數(shù)據(jù)庫(kù)名 表名1 表名2 > 備份文件路徑；
  • 備份多庫(kù)：mysqldump -uroot -p -B 數(shù)據(jù)庫(kù)名1 數(shù)據(jù)庫(kù)名2 ... > 備份文件路徑。

1.4.2 恢復(fù)（在 MySQL 客戶端執(zhí)行）

-- 恢復(fù)整個(gè)數(shù)據(jù)庫(kù)
source 備份文件路徑;

注意：若備份時(shí)未加-B參數(shù)，恢復(fù)前需先創(chuàng)建空數(shù)據(jù)庫(kù)并切換：

CREATE DATABASE IF NOT EXISTS mytest;
USE mytest;
source 備份文件路徑;

1.5 查看數(shù)據(jù)庫(kù)連接：排查并發(fā)問(wèn)題

當(dāng)數(shù)據(jù)庫(kù)響應(yīng)緩慢時(shí)，可查看當(dāng)前連接情況，排查異常連接（如被入侵）：

show processlist;

輸出樣例：

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  2 | root | localhost | test1| Sleep   | 120  |       | NULL             |
|  3 | root | localhost | NULL | Query   | 0    | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

• Command：連接狀態(tài)（Sleep為空閑，Query為執(zhí)行中）；
• Time：連接持續(xù)時(shí)間（秒）；
• Info：執(zhí)行的 SQL 語(yǔ)句。

二. 數(shù)據(jù)表（表）的核心操作

表是存儲(chǔ)數(shù)據(jù)的核心載體，表結(jié)構(gòu)的設(shè)計(jì)和修改直接影響業(yè)務(wù)開發(fā)，下面涵蓋表的創(chuàng)建、查看、修改、刪除全流程。

2.1 創(chuàng)建表：指定字段、類型、存儲(chǔ)引擎

創(chuàng)建表時(shí)需明確字段名、數(shù)據(jù)類型、字符集、存儲(chǔ)引擎等，同時(shí)可通過(guò)comment添加字段說(shuō)明。

2.1.1 語(yǔ)法格式

CREATE TABLE table_name (
  field1 datatype [comment '字段說(shuō)明'],
  field2 datatype [comment '字段說(shuō)明'],
  ...
) 
CHARACTER SET 字符集 
COLLATE 校驗(yàn)規(guī)則 
ENGINE 存儲(chǔ)引擎;

2.1.2 實(shí)戰(zhàn)案例

USE mytest;
CREATE TABLE users (
  id int comment '用戶ID',
  name varchar(20) comment '用戶名',
  password char(32) comment '密碼是32位的MD5加密值',
  birthday date comment '生日'
) 
CHARACTER SET utf8 
ENGINE MyISAM;

2.1.3 不同存儲(chǔ)引擎的文件差異

MySQL 支持插件式存儲(chǔ)引擎，不同引擎的表文件存儲(chǔ)格式不同：

• MyISAM（示例中使用）：
  • users.frm：表結(jié)構(gòu)文件；
  • users.MYD：表數(shù)據(jù)文件；
  • users.MYI：表索引文件；
• InnoDB（默認(rèn)引擎）：
  • users.frm：表結(jié)構(gòu)文件；
  • users.ibd：表數(shù)據(jù) + 索引文件（聚簇索引結(jié)構(gòu)）。

2.2 查看表結(jié)構(gòu)：驗(yàn)證表設(shè)計(jì)

-- 簡(jiǎn)潔查看表結(jié)構(gòu)
desc users;
-- 詳細(xì)查看表結(jié)構(gòu)（含注釋）
show create table users;

2.3 修改表：適配業(yè)務(wù)需求變更

項(xiàng)目開發(fā)中，表結(jié)構(gòu)需頻繁適配業(yè)務(wù)變更（如添加字段、修改字段類型等），ALTER TABLE是核心指令。

2.3.1 常用修改操作語(yǔ)法

2.3.2 實(shí)戰(zhàn)案例

USE mytest;
-- 1. 給users表添加字段assets（圖片路徑），放在birthday之后
ALTER TABLE users ADD assets varchar(100) comment '圖片路徑' AFTER birthday;
-- 2. 修改name字段長(zhǎng)度為60（適配更長(zhǎng)的用戶名）
ALTER TABLE users MODIFY name varchar(60);
-- 3. 刪除password字段（假設(shè)密碼存儲(chǔ)方式變更）
ALTER TABLE users DROP password;
-- 4. 修改表名為employee
ALTER TABLE users RENAME employee;
-- 5. 將name字段改為xingming（適配中文命名習(xí)慣）
ALTER TABLE employee CHANGE name xingming varchar(60);

2.3.3 注意事項(xiàng)

• 添加字段：新字段默認(rèn)允許為NULL，不會(huì)影響原有數(shù)據(jù)；
• 修改字段類型：若字段已有數(shù)據(jù)，需確保新類型兼容舊數(shù)據(jù)（如varchar轉(zhuǎn)int可能失敗）；
• 刪除字段：字段及對(duì)應(yīng)數(shù)據(jù)會(huì)永久刪除，需提前備份。

2.4 刪除表（謹(jǐn)慎操作?。?/h3>

刪除表會(huì)刪除表結(jié)構(gòu)和所有數(shù)據(jù)，無(wú)法恢復(fù)：

DROP TABLE IF EXISTS employee;

TEMPORARY：僅刪除臨時(shí)表（CREATE TEMPORARY TABLE創(chuàng)建的表）：

DROP TEMPORARY TABLE IF EXISTS temp_table;

三. 總結(jié)與避坑指南

本文覆蓋了 MySQL 庫(kù)與表的全流程操作，核心要點(diǎn)總結(jié)如下：

• 創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，建議明確指定CHARACTER SET utf8和校驗(yàn)規(guī)則，避免亂碼(也可以提前去自己配置好)；
• 校驗(yàn)規(guī)則決定字符串比較邏輯，需根據(jù)業(yè)務(wù)場(chǎng)景選擇（如用戶名是否區(qū)分大小寫）；
• 備份恢復(fù)是數(shù)據(jù)安全的保障，重要數(shù)據(jù)庫(kù)需定期備份，備份時(shí)建議添加-B參數(shù)；
• 修改表結(jié)構(gòu)時(shí)，刪除字段和修改字段類型需格外謹(jǐn)慎，避免數(shù)據(jù)丟失；
• 存儲(chǔ)引擎選擇：InnoDB 支持事務(wù)和行級(jí)鎖（默認(rèn)推薦），MyISAM 查詢速度快（適合只讀場(chǎng)景）。

常見避坑點(diǎn)：

• 庫(kù)名、表名、字段名避免使用 MySQL 關(guān)鍵字（如order、user），若必須使用需加反引號(hào) `；
• 備份時(shí)未加-B參數(shù)，恢復(fù)前需手動(dòng)創(chuàng)建數(shù)據(jù)庫(kù)并切換；
• 數(shù)據(jù)庫(kù)不支持直接修改庫(kù)名，需通過(guò) “備份→刪除舊庫(kù)→恢復(fù)為新庫(kù)名” 實(shí)現(xiàn)；
• 字段類型選擇需合理（如密碼用char(32)存儲(chǔ) MD5 值，生日用date類型），避免浪費(fèi)空間或存儲(chǔ)異常，關(guān)于類型問(wèn)題我們后面還會(huì)進(jìn)行更加詳細(xì)的學(xué)習(xí)。

到此這篇關(guān)于MySQL庫(kù)與表的DDL核心操作實(shí)戰(zhàn)案例的文章就介紹到這了,更多相關(guān)mysql庫(kù)與表ddl操作內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論