MySQL 表的基本操作，結合項目的表自動初始化來講

有了數據庫以后，我們就可以在數據庫中對表進行增刪改查了，這也就意味著，一名真正的 CRUD Boy 即將到來（😁）。

查表

查看當前數據庫中所有的表，使用 show tables; 命令

由于當前數據庫中還沒有表，所以輸出 Empty set。

不指定數據庫

在操作表之前，可以通過 use 數據庫名; 命令，指定要操作的數據庫。

那假如不指定數據庫的話，我們可以通過 show tables from test?的方式，來指定要操作的表，例如：

建表

既然沒有表，那我們就創建一張表吧。創建表的語法如下：

create table 表名(

列名1 數據類型1,

列名2 數據類型2,

...

列名n 數據類型n

);

例如，我們創建一張文章表，表中包含文章的標題、內容、作者、發布時間、閱讀量等信息，那么可以這樣創建：

create table article(id int primary key auto_increment,title varchar(100) not null,content text not null,author varchar(20) not null,create_time datetime not null,read_count int default 0);

article 是表名；
id 是主鍵，類型為 int，自增長；
title 是標題，類型為 varchar，長度為 100，不允許為空；
content 是內容，類型為 text，不允許為空；
author 是作者，類型為 varchar，長度為 20，不允許為空；
create_time 是發布時間，類型為 datetime，不允許為空；
read_count 是閱讀量，類型為 int，默認值為 0。

當然了，實際工作中，我們可能不會直接通過 SQL 語句來創建表，而是通過一些建表的工具，比如說 Navicat、DataGrip 等。以及 PowerDesigner、chiner 這種建模工具，關于工具的使用.

給表添加注釋

在建表的時候，我們可以給表添加注釋，語法如下：

create table 表名(

列名1 數據類型1 comment '注釋1',

列名2 數據類型2 comment '注釋2',

...

列名n 數據類型n comment '注釋n'

) comment '表注釋';

這樣方便我們在后期維護的時候，能夠更好的理解表的含義。

我們來一個簡單的例子，在之前的基礎上增加了一些字段的注釋和表注釋：

create table article(id int primary key auto_increment comment '主鍵',title varchar(100) not null comment '標題',content text not null comment '內容',author varchar(20) not null comment '作者',create_time datetime not null comment '發布時間',read_count int default 0 comment '閱讀量'
) comment '文章表';

假如表已經存在

由于之前 article 表已經創建了，這時候再執行上述語句，就會報錯 Table 'article' already exists：

為了避免這種情況，我們可以在建表的時候，先判斷表是否存在，如果不存在，再創建表，語法如下：

create table if not exists 表名(

列名1 數據類型1,

列名2 數據類型2,

...

列名n 數據類型n

);

實際的例子如下所示：

create table if not exists article(id int primary key auto_increment comment '主鍵',title varchar(100) not null comment '標題',content text not null comment '內容',author varchar(20) not null comment '作者',create_time datetime not null comment '發布時間',read_count int default 0 comment '閱讀量') comment '文章表';

刪表

刪除表的語法如下：

drop table 表名;

同樣的，在刪表的時候盡量眨眨眼😂，看看自己是不是被壞人給控制了，否則又是“刪庫跑路”的悲劇。

同樣，在刪除表的時候也可以加上 if exists，語法如下：

drop table if exists 表名;

這樣可以防止表不存在的時候，報錯。

查看表結構

有時候，我們想知道表的結構是什么樣的，也就是 create table 的時候包含了哪些列、列有哪些屬性，那這時候我們可以使用以下這些命令查看：

desc 表名;
describe 表名;
explain 表名;
show columns from 表名;
show fields from 表名;

結果都是一樣的，大家可以根據自己的喜好，記住其中的一個就行了。

還有一個命令 show create table 表名;，可以查看建表語句。

關于表的數據類型，比如說 int、varchar、datetime 等，這些我們會留到后面的章節來講。

那假如沒有使用 use 數據庫名; 命令指定要操作的數據庫，那我們可以通過 show columns from 數據庫名.表名; 的方式，來查看表的結構，例如：

show create table test.article;

; 結尾查詢到的信息格式比較亂，可以通過 ?\G 來格式化輸出，例如：

改表

通常來說，創建表之前就要做好充分的設計，盡量增加一些冗余字段來應對未來的需求變更，這樣整個程序的改動量是最小的，也不容易出現 bug。

因為改動表的結構，就意味著對應的 SQL 語句要改、程序的邏輯代碼要改、測試用例要改，很容易出現遺漏，導致程序出現意料之外的 bug。

所以，該表操作一定要慎重。

但又不能過度設計表，因為過度設計會導致表結構過于復雜，增加了維護成本，而且也不利于后期的擴展。

所以，改表操作也是一門學問。

增加字段

通常來說，增加字段是改表操作中最常見的操作，語法如下：

alter table 表名 add 列名數據類型;

例如，我們要給 article 表增加一個 update_time 字段，類型為 datetime，那么可以這樣寫：

alter table article add update_time datetime;

增加字段的位置

在上面的例子中，我們給 article 表增加了一個 update_time 字段，但是這個字段是添加到了最后，如果我們想要添加到某個字段的前面，那么可以這樣寫：

alter table article add update_time datetime after create_time;

刪除字段

刪除字段的語法如下：

alter table 表名 drop 列名;

例如，我們要刪除 article 表中的 update_time 字段，那么可以這樣寫：

alter table article drop update_time;

修改字段

修改字段發生的頻率其實很低，畢竟設計的時候，都是經過深思熟慮的，但是有時候，也會因為一些原因，需要修改字段，比如說字段類型、字段長度等。

修改字段的語法如下：

alter table 表名 modify 列名數據類型;

例如，我們要修改 article 表中的 title 字段，將類型從 varchar(100) 修改為 varchar(200)，那么可以這樣寫：

alter table article modify title varchar(200);

需要注意的是，如果表中已經存在數據了，那么修改表的時候就要格外注意，比如說原來的 title 字段的長度是 100，現在修改為 10，那么就容納不下原來的數據了，這時候就會報錯。

我們留到數據插入的時候再來演示。

修改字段名

有時候，可能我們設計表字段的時候犯了蠢，字段名起的不太好，這時候就需要修改字段名了。修改字段名的語法如下：

alter table 表名 change 原列名新列名數據類型;

例如，我們要修改 article 表中的 title 字段名為 article_title，那么可以這樣寫：

alter table article change title article_title varchar(100);

修改表名

同樣的，當我們需要修改表名時，可這么做：

alter table 原表名 rename 新表名;

例如，我們要將 article 表名修改為 article_info，那么可以這樣寫：

alter table article rename article_info;

技術派的表設計

整個庫表的設計，首先要先厘清楚業務和需求，然后再進行設計，這樣才能做到合理、高效。

技術派是如何初始化表的？

技術派的表初始化是借助 Liquibase 來實現的。

Liquibase 提供了一種結構化和系統化的方法來管理數據庫架構的更改。它通過變更日志和變更集來控制數據庫的狀態變化，確保了數據庫架構變更的可追溯性、可重復性和一致性，同時也支持跨團隊的協作。適用于持續集成和持續部署的環境。

第一，在不使用 liquibase 的情況下，實現表的初始化。

@Slf4jpublic class ForumDataSourceInitializerTest extends BasicTest {@Value("classpath:liquibase/data/init_schema_221209.sql")private Resource schemaSql;@Value("classpath:liquibase/data/init_data_221209.sql")private Resource initData;@Testpublic void dataSourceInitializer() throws SQLException {DataSource dataSource = createCustomDataSource();//getMetaData():數據庫元數據log.info(dataSource.getConnection().getMetaData().getURL());//初始化數據庫連接池final DataSourceInitializer initializer = new DataSourceInitializer();// 設置數據源initializer.setDataSource(dataSource);initializer.setEnabled(true);//自動執行sql腳本final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();populator.addScript(schemaSql);populator.addScript(initData);//設置數據庫填充器。initializer.setDatabasePopulator(populator);//初始化數據源。initializer.afterPropertiesSet();}private DataSource createCustomDataSource() {DriverManagerDataSource dataSource = new DriverManagerDataSource();//驅動器dataSource.setDriverClassName("com.mysql.jdbc.Driver");//數據庫urldataSource.setUrl("jdbc:mysql://localhost:3306/test");//用戶名dataSource.setUsername("root");//密碼dataSource.setPassword("123456");return dataSource;}
}

這里簡單解釋一下大家可能比較陌生的代碼：

①、通過 @Value 注解，將 init_schema_221209.sql 和 init_data_221209.sql 文件加載到 Resource 對象中。

@Value("classpath:liquibase/data/init_schema_221209.sql")

private Resource schemaSql;

@Value("classpath:liquibase/data/init_data_221209.sql")

private Resource initData;

schema.sql 為表結構文件，data.sql 為表數據文件。

②、createCustomDataSource 方法用于創建數據源，這里使用的是 Spring 提供的 DriverManagerDataSource，當然了，也可以使用 DruidDataSource、HikariDataSource 等。


private DataSource createCustomDataSource() {DriverManagerDataSource dataSource = new DriverManagerDataSource();dataSource.setDriverClassName("com.mysql.jdbc.Driver");dataSource.setUrl("jdbc:mysql://localhost:3306/test");dataSource.setUsername("root");dataSource.setPassword("123456");return dataSource;}

③、ResourceDatabasePopulator 是用于填充數據庫的工具類，實現了 DatabasePopulator 接口。主要用于執行 SQL 腳本文件，這些文件可以包含創建表、插入數據等 SQL 命令，也就是前面提到的 schema.sql 和 data.sql。

final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();

populator.addScript(schemaSql);

populator.addScript(initData);

④、DataSourceInitializer 用于在應用程序啟動時自動初始化數據庫。通過執行 SQL 腳本來完成這項工作。

final DataSourceInitializer initializer = new DataSourceInitializer();
// 設置數據源
initializer.setDataSource(dataSource);
// 設置是否啟用初始化
initializer.setEnabled(true);
// 設置填充數據庫的工具類
initializer.setDatabasePopulator(populator);
// 執行初始化
initializer.afterPropertiesSet();

執行該測試類后，可以在控制臺看到對應的日志信息。