MySQL 如何實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        MySQL 5.6 實(shí)現(xiàn)
        MySQL 5.7 實(shí)現(xiàn)
        MySQL 8.0 實(shí)現(xiàn)
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。我們?cè)谥暗奈恼轮薪榻B了如何在 PostgreSQL 中實(shí)現(xiàn)一個(gè)只能存儲(chǔ)一行數(shù)據(jù)的表。今天我們來(lái)看看如何在 MySQL 中實(shí)現(xiàn)相同的功能。

只有一行數(shù)據(jù)的表通常被用于某些特定的功能，例如存儲(chǔ)系統(tǒng)的版本或者配置信息，全局計(jì)數(shù)器，心跳表等。假如我們有一個(gè)表 t_visit，用于記錄網(wǎng)頁(yè)的訪問(wèn)量：

create table t_visit(
  hits bigint not null,
  update_at datetime not null
);



在部署程序時(shí)需要生成一條記錄，將訪問(wèn)量初始化為 0；以后每次用戶訪問(wèn)時(shí)需要加 1；為了前端頁(yè)面能夠正確顯示，不允許用戶刪除該記錄。這種需求該如何實(shí)現(xiàn)？
MySQL 5.6 實(shí)現(xiàn)

對(duì)于 MySQL 5.6 以及之前的版本，可以通過(guò)增加一個(gè) ENUM 枚舉字段和唯一約束限制表中只能存儲(chǔ)一行數(shù)據(jù)：

alter table t_visit add only_one_row enum('Y') not null default 'Y';
alter table t_visit add constraint uk_visit unique key (only_one_row);



新增加的字段 only_one_row 非空且取值只能為 Y，同時(shí)基于該字段創(chuàng)建了一個(gè)唯一約束，因此整個(gè)表中只能存儲(chǔ)一行數(shù)據(jù)。

初始化時(shí)插入數(shù)據(jù)如下：

insert into t_visit(hits, update_at) values (0, now());

select * from t_visit;
hits|update_at          |only_one_row|
----|-------------------|------------|
   0|2020-07-07 08:05:03|Y           |


如果數(shù)據(jù)已經(jīng)存在，再次插入數(shù)據(jù)將會(huì)返回錯(cuò)誤：

insert into t_visit(hits, update_at) values (1, now());
ERROR 1062 (23000): Duplicate entry 'Y' for key 't_visit.uk_visit'



實(shí)際上此時(shí)應(yīng)該進(jìn)行更新操作：

update t_visit
set hits=hits+1,
    update_at= now()
where only_one_row = 'Y';



或者，可以使用INSERT ... ON DUPLICATE KEY UPDATE語(yǔ)句進(jìn)行統(tǒng)一操作：

-- 清空數(shù)據(jù)
truncate table t_visit;

-- 第一次插入數(shù)據(jù)
insert into t_visit(hits, update_at) values (1, now())
on duplicate key update hits=ifnull(hits,0)+1, update_at= now();

select * from t_visit;
hits|update_at          |only_one_row|
----|-------------------|------------|
   1|2020-07-07 08:22:32|Y           |

-- 第 N 次插入數(shù)據(jù)
insert into t_visit(hits, update_at) values (1, now())
on duplicate key update hits=ifnull(hits,0)+1, update_at= now();

select * from t_visit;
hits|update_at          |only_one_row|
----|-------------------|------------|
   2|2020-07-07 08:22:59|Y           |



為了防止表中的數(shù)據(jù)被誤刪除，需要通過(guò)一個(gè)觸發(fā)器來(lái)實(shí)現(xiàn)禁止刪除操作：

delimiter $$
create trigger tri_disable_visit_del
  before delete on t_visit
  for each row
begin
  signal sqlstate '45000'
  set message_text = '禁止刪除訪問(wèn)信息！';
end$$
delimiter ;


該觸發(fā)器是一個(gè)針對(duì)刪除操作的 BEFORE 觸發(fā)器，在任何刪除操作之前返回錯(cuò)誤信息。創(chuàng)建觸發(fā)器之后，當(dāng)我們嘗試刪除表中的記錄：

delete
from t_visit
where only_one_row = 'Y';
ERROR 1644 (45000): 禁止刪除訪問(wèn)信息！

select * from t_visit;
hits|update_at          |only_one_row|
----|-------------------|------------|
   2|2020-07-07 08:22:59|Y           |



刪除語(yǔ)句返回了錯(cuò)誤信息，表中的數(shù)據(jù)仍然存在。不過(guò)，TRUNCATE TABLE 語(yǔ)句仍然可以清除表中的數(shù)據(jù)，因?yàn)樗粫?huì)觸發(fā) DML 觸發(fā)器。
MySQL 5.7 實(shí)現(xiàn)

MySQL 5.7 除了使用前面的方法之外，還可以利用新增加的計(jì)算列（Generated Column）加唯一約束的方式。例如：

-- 刪除 t_visit 表
drop table t_visit;

create table t_visit(
  hits bigint not null,
  update_at datetime not null,
  only_one_row int as (1) unique
);



其中，字段 only_one_row 是一個(gè)虛擬的計(jì)算列，同時(shí)具有唯一性。插入數(shù)據(jù)的結(jié)果如下：

insert into t_visit(hits, update_at) values (0, now());

select * from t_visit;
hits|update_at          |only_one_row|
----|-------------------|------------|
   0|2020-07-07 09:22:13|           1|

insert into t_visit(hits, update_at) values (1, now());
ERROR 1062 (23000): Duplicate entry '1' for key 't_visit.only_one_row'



同樣，我們也可以使用INSERT ... ON DUPLICATE KEY UPDATE語(yǔ)句進(jìn)行統(tǒng)一操作，具體參考上文中的 MySQL 5.6 實(shí)現(xiàn)。

利用觸發(fā)器可以防止表中的數(shù)據(jù)被誤刪除，具體參考上文中的 MySQL 5.6 實(shí)現(xiàn)。
MySQL 8.0 實(shí)現(xiàn)

MySQL 8.0.13 之后除了可以使用上面介紹的兩種方法之外，還可以通過(guò)函數(shù)索引實(shí)現(xiàn)相同的功能。例如：

create table t_visit(
  hits bigint not null,
  update_at datetime not null
);

create unique index uk_visit on t_visit( (1) );



其中，索引 uk_visit 是一個(gè)基于常量 1 的函數(shù)索引，并且具有唯一性。函數(shù)索引實(shí)際上是虛擬列索引的擴(kuò)展，因此兩者非常相似。

插入數(shù)據(jù)的結(jié)果如下：

insert into t_visit(hits, update_at) values (0, now());

select * from t_visit;
hits|update_at          |only_one_row|
----|-------------------|------------|
   0|2020-07-07 09:22:13|           1|

insert into t_visit(hits, update_at) values (1, now());
ERROR 1062 (23000): Duplicate entry '1' for key 't_visit.uk_visit'


同樣，我們也可以使用INSERT ... ON DUPLICATE KEY UPDATE語(yǔ)句進(jìn)行統(tǒng)一操作，具體參考上文中的 MySQL 5.6 實(shí)現(xiàn)。

利用觸發(fā)器可以防止表中的數(shù)據(jù)被誤刪除，具體參考上文中的 MySQL 5.6 實(shí)現(xiàn)。
總結(jié)

在 MySQL 中為了實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表，可以創(chuàng)建一個(gè)數(shù)值為常量的字段，并且基于該字段創(chuàng)建唯一索引。ENUM 枚舉數(shù)據(jù)類型和計(jì)算列都可以用于實(shí)現(xiàn)該功能，MySQL 8.0 新增的函數(shù)索引甚至不需要?jiǎng)?chuàng)建額外的字段就可以實(shí)現(xiàn)相同的功能。

除了本文使用的方法之外，你還有沒(méi)有其他的實(shí)現(xiàn)方法？歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??