<p>可能实现</p>
<ol>
<li>创建数据表
<blockquote>
<pre><code>CREATE TABLE data (id CHAR(9) NOT NULL DEFAULT '',
val TEXT,
PRIMARY KEY (id));
</code></pre>
</blockquote></li>
<li>创建服务表
<blockquote>
<pre><code>CREATE TABLE ids (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
</code></pre>
</blockquote></li>
<li>创建生成<code>id</code>值的触发器
<blockquote>
<pre><code>CREATE TRIGGER tr_bi_data
BEFORE INSERT
ON data
FOR EACH ROW
BEGIN
INSERT INTO ids () VALUES ();
SET NEW.id = CONCAT('CS', LPAD(LAST_INSERT_ID(), 7, '0'));
DELETE FROM ids;
END
</code></pre>
</blockquote></li>
<li>创建禁止<code>id</code>值更改的触发器
<blockquote>
<pre><code>CREATE TRIGGER tr_bu_data
BEFORE UPDATE
ON data
FOR EACH ROW
BEGIN
SET NEW.id = OLD.id;
END
</code></pre>
</blockquote></li>
<li>插入一些数据,检查结果
<blockquote>
<pre><code>INSERT INTO data (val) VALUES ('data-1'), ('data-2');
SELECT * FROM data;
</code></pre>
<pre>
id | val
: | : -
CS0000001 | data-1
CS0000002 | data-2
</pre>
</blockquote></li>
<li>尝试更新,确保<code>id</code>更改被禁止
<blockquote>
<pre><code>UPDATE data SET id = 'CS0000100' WHERE val = 'data-1';
SELECT * FROM data;
</code></pre>
<pre>
id | val
: | : -
CS0000001 | data-1
CS0000002 | data-2
</pre>
</blockquote></li>
<li>再插入一个数据,确保枚举继续
<blockquote>
<pre><code>INSERT INTO data (val) VALUES ('data-3'), ('data-4');
SELECT * FROM data;
</code></pre>
<pre>
id | val
: | : -
CS0000001 | data-1
CS0000002 | data-2
CS0000003 | data-3
CS0000004 | data-4
</pre>
</blockquote></li>
<li>检查服务表是否已成功清除
<blockquote>
<pre><code>SELECT COUNT(*) FROM ids;
</code></pre>
<pre>
| COUNT(*) |
| -: |
| 0 |
</pre>
</blockquote></li>
</ol>
<p><em>db<&燃气轮机;小提琴<a href="https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ac51d9b157995edb1a002cdd1fef959e" rel="nofollow noreferrer">here</a></em></p>
<p><strong>缺点:</strong></p>
<ol>
<li>需要额外的表格</李>
<li>已禁用生成的<code>id</code>值编辑(必须改为使用复制和删除旧记录,无法设置自定义值)</李>
</ol>