一.庫的操作

1.1 創(chuàng)建數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫：create database db_name;本質就是在 /var/lib/mysql 創(chuàng)建一個目錄

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

說明：

• 大寫的表示關鍵字
• [] 是可選項
• CHARACTER SET: 指定數(shù)據(jù)庫采用的字符
• COLLATE: 指定數(shù)據(jù)庫字符集的校驗規(guī)則

創(chuàng)建數(shù)據(jù)庫案例的指令

• 創(chuàng)建名為 db1 的數(shù)據(jù)庫
  create database db1;

• 當我們創(chuàng)建數(shù)據(jù)庫沒有指定字符集和校驗規(guī)則時，系統(tǒng)使用默認字符集：utf8，校驗規(guī)則是：utf8_general_ci

• 創(chuàng)建一個使用utf8字符集的db2數(shù)據(jù)庫
  create database db2 charset=utf8;

• 創(chuàng)建一個使用utf字符集，并帶校對規(guī)則的db3數(shù)據(jù)庫。
  create database db3 charset=utf8 collate utf8_general_ci;

我最初創(chuàng)建的例子

1-2刪除數(shù)據(jù)庫

DROP DATABASE [IF EXISTS] db_ name;

執(zhí)行刪除之后的結果:
數(shù)據(jù)庫內部看不到對應的數(shù)據(jù)庫
對應的數(shù)據(jù)庫文件夾被刪除，級聯(lián)刪除，里面的數(shù)據(jù)表全部被刪
注意：不要隨意刪除數(shù)據(jù)庫

1-3認識mysql的編碼

創(chuàng)建數(shù)據(jù)庫的時候，有兩個編碼集：

1. 數(shù)據(jù)庫編碼集 —— 數(shù)據(jù)庫未來存儲數(shù)據(jù)

2. 數(shù)據(jù)庫校驗集 —— 支持數(shù)據(jù)庫進行字段比較使用的編碼，本質也是一種讀取數(shù)據(jù)庫中數(shù)據(jù)所采用的編碼格式

數(shù)據(jù)庫無論對數(shù)據(jù)做任何操作，都必須保證操作和編碼是一致的！

1-3-1字符集和校驗規(guī)則

 查看系統(tǒng)默認字符集以及校驗規(guī)則

show variables like 'character_set_database';
show variables like 'collation_database';

utf8mb4_0900_ai_ci 的含義：

• utf8mb4：編碼集（支持完整Unicode，包括emoji）

• 0900：基于Unicode 9.0標準

• ai：口音不敏感（accent insensitive）

• ci：大小寫不敏感（case insensitive

• show variables like 'collation_database';，這條指令用于查看當前數(shù)據(jù)庫的校驗規(guī)則。
•  show variables like 'collation_%';，這條指令用于查看所有以 collation 開頭的系統(tǒng)變量。這里的 % 是一個通配符，代表任意字符，所以它會顯示出包括連接、數(shù)據(jù)庫、服務器等各級別的校驗規(guī)則。

1-3-2查看數(shù)據(jù)庫支持的字符集

show charset;

字符集主要是控制用什么語言。比如utf8就可以使用中文。

1-3-3查看數(shù)據(jù)庫支持的字符集校驗規(guī)則

show collation;

1-4創(chuàng)建數(shù)據(jù)庫時的編碼設置

1. 默認創(chuàng)建（不指定編碼）

create database d1;

當創(chuàng)建數(shù)據(jù)庫沒有指定字符集和校驗規(guī)則時：

• 系統(tǒng)使用默認字符集：utf8

• 校驗規(guī)則是：utf8_general_ci

在 Linux 底層，MySQL 會在 /var/lib/mysql/ 目錄下創(chuàng)建對應的數(shù)據(jù)庫文件夾，里面會生成一個 db.opt 文件記錄編碼信息：

centos7

[root@VM-8-5-centos mysql]# cat d1/db.opt
default-character-set=utf8
default-collation=utf8_general_ci

2. 指定字符集創(chuàng)建數(shù)據(jù)庫

create database d2 charset=utf8;
create database d3 charset=utf8;

兩種寫法效果相同，查看底層文件：

[root@VM-0-3-centos mysql]# cat d2/db.opt
default-character-set=utf8
default-collation=utf8_general_ci

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

create database d4 charset=utf8 collate utf8_general_ci;

4. 使用 GBK 字符集創(chuàng)建數(shù)據(jù)庫

create database d5 charset=gbk collate gbk_chinese_ci;

查看 GBK 編碼的數(shù)據(jù)庫配置：

[root@VM-0-3-centos mysql]# cat d5/db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci

ubbuntu22.04

ubuntu22.04不能進行cat操作，所以我選用的是mysql操作這個可以進行

MySQL數(shù)據(jù)庫 db.opt 文件缺失問題排查（CentOS7  vs Ubuntu 22.04）

  問題背景 

我在學習MySQL數(shù)據(jù)庫時，執(zhí)行 CREATE DATABASE d1; 創(chuàng)建了數(shù)據(jù)庫，想通過查看 /var/lib/mysql/d1/db.opt 文件來確認字符集和校驗規(guī)則，但在Ubuntu 22.04環(huán)境中遇到了問題：
 
MySQL中 SHOW DATABASES 能看到 d1 ，且數(shù)據(jù)庫內有數(shù)據(jù)、可正常使用
Bash中 /var/lib/mysql/ 下存在 d1 目錄，但目錄內沒有 db.opt 文件
修改目錄權限也無法解決，而CentOS 7環(huán)境中可以正常看到該文件

 環(huán)境差異說明

 環(huán)境 MySQL版本 系統(tǒng)特性  db.opt 文件表現(xiàn) 
CentOS 7中的MySQL 5.7 傳統(tǒng)發(fā)行版，MySQL數(shù)據(jù)目錄權限嚴格 創(chuàng)建數(shù)據(jù)庫后自動生成 db.opt  
Ubuntu 22.04 MySQL 5.7（或8.0+） Debian系發(fā)行版，MySQL默認配置有差異 可能出現(xiàn) db.opt 未生成的情況 
 
補充：MySQL 8.0+版本徹底取消了 db.opt 文件，字符集配置統(tǒng)一存儲在系統(tǒng)表 mysql.schemata 中，這是Ubuntu 22.04默認安裝MySQL 8.0時的常見情況。 

 原因分析 

核心原因
 db.opt 是MySQL 5.7及之前版本中，創(chuàng)建數(shù)據(jù)庫時自動生成的物理文件，用于記錄該數(shù)據(jù)庫的默認字符集和校驗規(guī)則。

CentOS 7中MySQL 5.7的默認安裝流程完整，創(chuàng)建數(shù)據(jù)庫時會正常生成該文件
Ubuntu 22.04中可能因為安裝配置差異、權限初始化問題、創(chuàng)建時異常中斷，導致 db.opt 未生成，即使 db.opt 缺失， SHOW DATABASES 從系統(tǒng)表讀取數(shù)據(jù)，所以仍能顯示數(shù)據(jù)庫存在
 d1 不是系統(tǒng)自帶數(shù)據(jù)庫，是手動創(chuàng)建的，不會默認生成 db.opt 
 修改權限無法生成 db.opt ，因為該文件僅在創(chuàng)建數(shù)據(jù)庫時自動生成，后期權限調整不會觸發(fā)重新生成

 解決方案

 方案1：用SQL命令直接查看字符集（無需依賴 db.opt ）

 這是最穩(wěn)妥、跨版本通用的方法，無論是否有 db.opt 都能生效

-查看d1數(shù)據(jù)庫的字符集和校驗規(guī)則

SHOW CREATE DATABASE d1;

 方案2：手動創(chuàng)建 db.opt 文件（MySQL 5.7適用）

 如果需要恢復 db.opt 文件，可以手動創(chuàng)建并配置權限：

# 進入d1數(shù)據(jù)庫目錄
cd /var/lib/mysql/d1
# 寫入字符集配置
echo "default-character-set=utf8" > db.opt
echo "default-collation=utf8_general_ci" >> db.opt
# 修正文件權限（必須為mysql:mysql，否則MySQL無法讀?。?
chown mysql:mysql db.opt
chmod 660 db.opt
# 重啟MySQL服務生效
systemctl restart mysql

方案3：重建數(shù)據(jù)庫（適合有數(shù)據(jù)備份的情況）

# 1. 備份d1數(shù)據(jù)庫數(shù)據(jù)
mysqldump -u root -p d1 > d1_backup.sql
# 2. 登錄MySQL刪除d1
mysql -u root -p
DROP DATABASE d1;
# 3. 重新創(chuàng)建d1（指定字符集，確保生成db.opt）
CREATE DATABASE d1 CHARACTER SET utf8 COLLATE utf8_general_ci;
# 4. 恢復數(shù)據(jù)
mysql -u root -p d1 < d1_backup.sql

總結:

1. 版本差異：MySQL 8.0+不再生成 db.opt ，字符集查看請用 SHOW CREATE DATABASE 
2. 環(huán)境差異：CentOS和Ubuntu的MySQL安裝配置不同，可能導致 db.opt 生成異常
3. 最佳實踐：優(yōu)先使用SQL命令查看字符集，避免依賴物理文件； db.opt 缺失不影響數(shù)據(jù)庫正常使用
4. 思路：先確認數(shù)據(jù)庫邏輯存在→再查物理目錄→最后針對性解決

1-5校驗規(guī)則對數(shù)據(jù)庫的影響

• 不區(qū)分大小寫

創(chuàng)建一個數(shù)據(jù)庫，校驗規(guī)則使用utf8_ general_ ci不區(qū)分大小寫

create database test1 collate utf8_general_ci;
use test1;
create table person(name varchar(20));
insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');

不區(qū)分大小寫的查詢以及結果

mysql> use test1;
mysql> select * from person where name='a';

• 插入數(shù)據(jù)并查詢數(shù)據(jù)

• 結果排序

不區(qū)分大小寫排序以及結果：

mysql> use test1;
mysql> select * from person order by name;

select 是從表里查詢數(shù)據(jù)，通配符 * 代表將表里的所有列數(shù)據(jù)都顯示出來。

插入的本質是存儲數(shù)據(jù)，采用 default-character-set=utf8，也就是插入數(shù)據(jù)時所用的編碼格式。

查詢的本質是按照校驗集的要求來查找和比較數(shù)據(jù)。具體來說：

• 插入數(shù)據(jù)時：使用編碼集將數(shù)據(jù)編碼后存入磁盤

• 查詢數(shù)據(jù)時：使用校驗集的規(guī)則來比較字段（如 where 條件、order by 排序、group by 分組等）

兩者必須匹配：如果插入時用的編碼集和查詢時用的校驗集不兼容，就會導致比較結果出錯，甚至查不到本該存在的數(shù)據(jù)。

• 區(qū)分大小寫

創(chuàng)建一個數(shù)據(jù)庫，校驗規(guī)則使用utf8_ bin區(qū)分大小寫

create database test2 collate utf8_bin;
use test2
create table person(name varchar(20));
insert into person values('a');
insert into person values('A');
insert into person values('b');
insert into person values('B');

• 進行查詢

區(qū)分大小寫的查詢以及結果

mysql> use test2;
mysql> select * from person where name='a';

• 結果排序

區(qū)分大小寫排序以及結果：

mysql> use test2;
mysql> select * from person order by name;

注意：這里一般默認是升序排序。

1-6數(shù)據(jù)庫的刪改查

數(shù)據(jù)庫刪除

DROP DATABASE [IF EXISTS] db_ name;

執(zhí)行刪除之后的結果:
數(shù)據(jù)庫內部看不到對應的數(shù)據(jù)庫
對應的數(shù)據(jù)庫文件夾被刪除，級聯(lián)刪除，里面的數(shù)據(jù)表全部被刪
注意：不要隨意刪除數(shù)據(jù)庫

顯示創(chuàng)建語句

show create database 數(shù)據(jù)庫名;

說明：
MySQL 建議我們關鍵字使用大寫，但是不是必須的。
數(shù)據(jù)庫名字的反引號``,是為了防止使用的數(shù)據(jù)庫名剛好是關鍵字
有點版本比較低有這個情況/*!40100 default.... */ 這個不是注釋，表示當前mysql版本大于4.01版本，就執(zhí)行這句話

修改數(shù)據(jù)庫

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

• 將 test2 數(shù)據(jù)庫字符集改成 gbk

這個是在centos5.7下運行的可以用cat查到

mysql> alter database test2 charset=gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

[root@VM-0-3-centos mysql]# cat test2/db.opt
default-character-set=utf8
default-collation=utf8_bin
[root@VM-0-3-centos mysql]# cat test2/db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci

ubuntu22.04直接在mysql上查

-- 查看當前數(shù)據(jù)庫的字符集和校驗規(guī)則
show variables like 'character_set_database';
show variables like 'collation_database';

-- 或者查看數(shù)據(jù)庫的創(chuàng)建語句（會顯示編碼信息）
show create database test2;

說明：
對數(shù)據(jù)庫的修改主要指的是修改數(shù)據(jù)庫的字符集，校驗規(guī)則

查看數(shù)據(jù)庫

show databases;

查詢當前所在數(shù)據(jù)庫：

select database();

1-7數(shù)據(jù)庫備份和恢復

1.備份

語法：

# mysqldump -P3306 -u root -p 密碼 -B 數(shù)據(jù)庫名 > 數(shù)據(jù)庫備份存儲的文件路徑;

# -P 端口號（大寫P，默認3306可省略）
# -p 密碼（小寫p，直接跟密碼，中間沒有空格）

mysqldump -P3306 -u root -p123456 -B test1 > test1.sql

為了避免密碼暴露在命令行中（會被 history 記錄），更安全的做法是：
# 不寫密碼，執(zhí)行后會提示交互式輸入
mysqldump -P3306 -u root -p -B test1 > D:/test1.sql

# 然后系統(tǒng)會提示：Enter password: 你輸入密碼（不顯示）

我們將test1進行備份

這個文件實際上就是把我們的數(shù)據(jù)庫相關的所有數(shù)據(jù)，以及表等放到這個文件當中也可以稱為物理文件

2.還原

語法：

mysql> source /root/MySQL/test1.sql

我剛直接輸入指令西顯示沒有文件存在，我就先if了一下，確定恢復時沒有先確保沒有任何數(shù)據(jù)庫占用沖突，你直接 source 的時候，如果 test1 數(shù)據(jù)庫已經(jīng)存在，可能會報錯或者跳過。

兩種恢復方式的區(qū)別

情況一：備份時帶了 -B

mysqldump -u root -p -B test1 > test1.sql;

恢復時直接：

source /root/MySQL/mytest.sql;

不需要先建庫、不需要 use，因為文件里已經(jīng)有 CREATE DATABASE 和 USE 語句。

情況二：備份時沒帶 -B

mysqldump -u root -p test1 > test1.sql;

恢復時必須先手動創(chuàng)建數(shù)據(jù)庫：

create database mytest;
use mytest;
source /root/MySQL/mytest.sql;

如果你直接 source 而沒有先建庫和 use，就會出問題。

3. 注意事項

如果備份的不是整個數(shù)據(jù)庫，而是其中的一張表，怎么做？

 mysqldump -u root -p 數(shù)據(jù)庫名 表名1 表名2 > test1.sql;

同時備份多個數(shù)據(jù)庫

mysqldump -u root -p -B 數(shù)據(jù)庫名1 數(shù)據(jù)庫名2 ... > 數(shù)據(jù)庫存放路徑;

如果備份一個數(shù)據(jù)庫時，沒有帶上-B參數(shù)， 在恢復數(shù)據(jù)庫時，需要先創(chuàng)建空數(shù)據(jù)庫，然后使用數(shù)據(jù)庫，再使用source來還原。

4. 查看連接情況

語法：

show processlist;

二.表的操作

2-1創(chuàng)建表

語法：

CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校驗規(guī)則 engine 存儲引擎;

說明：
field 表示列名
datatype 表示列的類型
character set 字符集，如果沒有指定字符集，則以所在數(shù)據(jù)庫的字符集為準
collate 校驗規(guī)則，如果沒有指定校驗規(guī)則，則以所在數(shù)據(jù)庫的校驗規(guī)則為準

說明：
不同的存儲引擎，創(chuàng)建表的文件不一樣。
users 表存儲引擎是 MyISAM ，在數(shù)據(jù)目中有三個不同的文件，分別是：
users.frm：表結構
users.MYD：表數(shù)據(jù)
users.MYI：表索引

示例（MySQL 5.7）

不同的存儲引擎，創(chuàng)建表的文件不一樣。

(1) user1 表存儲引擎是 MyISAM

在數(shù)據(jù)目錄中有三個不同的文件；

[root@VM-0-3-centos user_db]# ll
total 128
-rw-r--r--  1 mysql mysql    61 May 19 9:00 db.opt
-rw-r--r--  1 mysql mysql    8645 May 19 9:02 user1.frm
-rw-r--r--  1 mysql mysql    0 May 19 9:02 user1.MYD
-rw-r--r--  1 mysql mysql   1024 May 19 9:02 user1.MYI

• user1.frm：表結構

• user1.MYD：表數(shù)據(jù)

• user1.MYI：表索引

(2) user2 表存儲引擎是 InnoDB

在數(shù)據(jù)目錄中有兩個不同的文件：

[root@VM-0-3-centos user_db]# ll
total 128
-rw-r--r--  1 mysql mysql    61 May 19 9:00 db.opt
-rw-r--r--  1 mysql mysql    8645 May 19 9:02 user1.frm
-rw-r--r--  1 mysql mysql    0 May 19 9:02 user1.MYD
-rw-r--r--  1 mysql mysql    1024 May 19 9:02 user1.MYI
-rw-r--r--  1 mysql mysql    8645 May 19 9:02 user2.frm
-rw-r--r--  1 mysql mysql    98304 May 19 9:02 user2.ibd

• user2.frm：表結構

• user2.ibd：表數(shù)據(jù)和索引

創(chuàng)建 user3 表（默認存儲引擎）

mysql> create table if not exists user3(name char(32));
Query OK, 0 rows affected (0.02 sec)

查看文件：

[root@VM-0-3-centos user_db]# ll
total 236
-rw-r--r--  1 mysql mysql    61 May 19 9:02 db.opt
-rw-r--r--  1 mysql mysql    8645 May 19 9:02 user1.frm
-rw-r--r--  1 mysql mysql    0 May 19 9:02 user1.MYD
-rw-r--r--  1 mysql mysql   1024 May 19 9:02 user1.MYI
-rw-r--r--  1 mysql mysql   8645 May 19 9:02 user2.frm
-rw-r--r--  1 mysql mysql   98304 May 19 9:02 user2.ibd
-rw-r--r--  1 mysql mysql   8560 May 19 9:03 user3.frm
-rw-r--r--  1 mysql mysql   98304 May 19 9:03 user3.ibd

表默認的存儲引擎是 InnoDB。

ubuntu

 Ubuntu 輸出可以看到：

• user1 表同時存在 user1.MYD、user1.MYI 和 user1_369.sdi 文件

• user2 表只有 user2.ibd 文件

這說明：

在 MySQL 8.0 中，即使你指定 engine=MyISAM 創(chuàng)建了表，它依然會生成一個 .sdi 文件（替代舊版的 .frm），同時也會生成 .MYD 和 .MYI 文件。但默認存儲引擎是 InnoDB，所以不指定引擎時創(chuàng)建的表（如 user3、person）只會生成 .ibd 文件。

你實際看到的文件對應關系

為什么 user1 沒有 .frm 而是 .sdi？

 Ubuntu 裝的是 MySQL 8.0，而筆記里的 CentOS 是 MySQL 5.7。

• MySQL 5.7 及之前：表結構存在 .frm 文件

• MySQL 8.0：表結構存在數(shù)據(jù)字典中，同時導出為 .sdi（序列化字典信息）文件

所以找不到 user1.frm 是正常的，8.0 里對應的是 user1_369.sdi。

備注：創(chuàng)建一個engine是innodb的數(shù)據(jù)庫，觀察存儲目錄

2-2查看表

查看表結構

desc 表名;

顯示表的詳細信息

show create table user1;
show create table user1 \G;（\G：格式化顯示，把不需要的符號去掉。）

\G：格式化顯示，去掉不需要的符號。

2-3修改表

在項目實際開發(fā)中，經(jīng)常修改某個表的結構，比如字段名字，字段大小，字段類型，表的字符集類型，表的存儲引擎等等。我們還有需求，添加字段，刪除字段等等。這時我們就需要修改表。

語法：

LTER TABLE tablename ADD (column datatype [DEFAULT expr][,column
datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column
datatype]...);
ALTER TABLE tablename DROP (column);

• 將user1改為user(to可以省略）

• 在表中添加記錄

insert into tablename values (插入各項內容);

• 在表中添加一個字段

alter table tablename add (column datatype [DEFAULT expr][,column datatype]...);

插入新字段后，對原來表中的數(shù)據(jù)沒有影響：

2-4修改屬性

alter table tablename modify (column datatype [DEFAULT expr][,column datatype]...);

不是指定進行修改，所以要將所修改屬性后面的內容也加上，否則將直接覆蓋原有內容。

2-5刪除password列

注意：刪除字段一定要小心，刪除字段及其對應的列數(shù)據(jù)都沒了

alter table tablename drop (column);

2-6修改列名

alter table tablename change (column newcolumn datatype [DEFAULT expr][,column datatype]...);

新字段需要完整定義，屬性也要帶上

2-7刪除表

語法格式：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

總結

到此這篇關于mysql庫和表的操作方法超詳細講解的文章就介紹到這了,更多相關mysql庫和表操作方法內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論