前言：

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

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

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

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

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

1.1.1 語法格式

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

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

1.1.2 實戰(zhàn)案例

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

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

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

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

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

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

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

1.2.3 校驗規(guī)則的實際影響

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

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

案例演示：

-- 1. 創(chuàng)建不區(qū)分大小寫的數(shù)據(jù)庫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ù)庫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';
-- 排序：按二進制ASCII碼排序（大寫在前，小寫在后）
SELECT * FROM person ORDER BY name;

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

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

show databases;

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

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

show create database db3;

輸出樣例：

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

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

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

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

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

1.3.4 刪除數(shù)據(jù)庫（謹慎操作！）

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

DROP DATABASE IF EXISTS db3;

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

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

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

• 語法：

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

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

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

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

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

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

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

當數(shù)據(jù)庫響應(yī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ù)時間（秒）；
• Info：執(zhí)行的 SQL 語句。

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

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

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

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

2.1.1 語法格式

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

2.1.2 實戰(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 不同存儲引擎的文件差異

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

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

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

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

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

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

2.3.1 常用修改操作語法

2.3.2 實戰(zhàn)案例

USE mytest;
-- 1. 給users表添加字段assets（圖片路徑），放在birthday之后
ALTER TABLE users ADD assets varchar(100) comment '圖片路徑' AFTER birthday;
-- 2. 修改name字段長度為60（適配更長的用戶名）
ALTER TABLE users MODIFY name varchar(60);
-- 3. 刪除password字段（假設(shè)密碼存儲方式變更）
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 注意事項

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

2.4 刪除表（謹慎操作?。?/h3>

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

DROP TABLE IF EXISTS employee;

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

DROP TEMPORARY TABLE IF EXISTS temp_table;

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

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

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

常見避坑點：

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

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

最新評論