MySQL使用65位VARCHAR作为唯一主键的方案疑问及优化咨询

咱们先聊聊你当前这个查询的潜在问题，再说说更贴合生产场景的优化思路：

一、当前查询的隐患 #

• 并发冲突风险：如果多个请求同时执行这个插入语句，会出现竞态条件——多个会话会同时读到相同的MAX(USERID)值，最终插入重复的USERID，触发主键/唯一约束冲突。这是因为MySQL默认事务隔离级别下，不同会话的MAX()查询可能读取到未提交的脏数据。
• 冗余子查询拖慢性能：你写的(SELECT * FROM users) AS myusers完全是多余的，MAX()函数可以直接作用于原表，这个子查询会额外触发一次全表扫描，数据量大时会明显拖慢查询速度。
• 数据类型容错性差：如果USERID字段不小心存入了非数字字符（比如误操作插入字母、符号），CAST(USERID AS DECIMAL(65,0))会直接报错，导致插入失败；如果USERID的数字长度超过65位，转换还会溢出报错。

二、更优的实现方式 #

1. 最小改动：优化当前查询 #

先去掉冗余的子查询，简化后的语句能减少一次全表扫描，提升基础性能（但并发问题依然存在，适合低并发临时场景）：

INSERT INTO users (USERID, UNIQUECODE) 
VALUES(
  (SELECT COALESCE(MAX(CAST(USERID AS DECIMAL(65, 0))), 1) + 1 FROM users), 
  'NEW 1'
);

2. 解决并发：事务加锁（无需改表） #

在事务中使用SELECT ... FOR UPDATE锁住表，确保同一时间只有一个会话能获取最新的MAX值：

START TRANSACTION;
-- 锁住users表，防止其他会话同时读取MAX值
SELECT COALESCE(MAX(CAST(USERID AS DECIMAL(65, 0))), 1) + 1 INTO @nextid FROM users FOR UPDATE;
INSERT INTO users (USERID, UNIQUECODE) VALUES(@nextid, 'NEW 1');
COMMIT;

注意：FOR UPDATE执行MAX()时会锁住整个users表，高并发场景下可能导致锁等待，影响整体性能。

3. 推荐方案：用序列生成主键（MySQL 8.0+） #

如果你的MySQL版本是8.0.1及以上，直接用序列生成唯一递增数字，完全避免全表扫描和并发冲突：

-- 创建序列，起始值1，步长1
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;

-- 插入时取序列下一个值，转成字符串存USERID
INSERT INTO users (USERID, UNIQUECODE) 
VALUES(CAST(NEXTVAL(user_seq) AS CHAR), 'NEW 1');

序列是MySQL原生支持的自增生成器，性能比每次查MAX高得多，且天然解决并发问题。

4. 长期最优：调整表结构 #

如果业务允许修改表结构，建议直接将USERID改为BIGINT AUTO_INCREMENT（支持最大9e18，足够大部分场景）；若确实需要65位数字，可配合序列生成DECIMAL(65,0)类型的主键。这样既保证主键唯一递增，又省去了类型转换和全表扫描的开销。

如果不需要递增主键，也可以用UUID()生成唯一字符串主键：

INSERT INTO users (USERID, UNIQUECODE) VALUES(UUID(), 'NEW 1');

UUID的优点是无需依赖表状态，分布式场景下也能生成唯一值，但索引性能比递增主键稍差。

内容的提问来源于stack exchange，提问作者Ajmal Muhammad P