时间戳

首页 > 解决方案 > SQLite 查询问题，以及事务的原子性

sql - SQLite 查询问题，以及事务的原子性

问题描述

问题：我有两个表（members1和members2），它们只是用户名列表。我需要更改 中的用户名members1，但前提是新用户名尚未出现在任一表中（换句话说，用户名在两个表中必须是唯一的）。

SQL 超出了我的舒适区，但经过几个小时的谷歌搜索后，我想出了下面的查询。我实际上使用的是 SQLite 的 C API，所以这只是一个用于检查语法的测试 shell 脚本。该脚本将旧名称更改为新名称，其中$1旧名称为旧名称，$2新名称为：

#!/usr/bin/env bash
sqlite3 test.db <<EOF

update members1 set uname=
  case 
    exists(select 1 from members1 where uname='$2') or
    exists(select 1 from members2 where uname='$2')
  when 1 then '$1'
  else '$2'
  end
where uname='$1';

EOF

问题：

  1. 这似乎有效，但它有意义吗？似乎很啰嗦。有一个更好的方法吗？特别是如果出现名称冲突，是否真的需要更新members1才能将旧名称改回自己？
  2. 如果多个进程同时做同样的事情，这有多安全？进程 A 是否可以将 'john' 更改为 'jim'，而进程 B 同时将 'jo' 更改为 'jim'，从而使 'jim' 在数据库中出现两次？
  3. 实际上大写/小写的协议是什么？上面的代码运行没有问题，但网络上的所有内容都是大写的。

谢谢。

标签： sqlsqliteatomic

解决方案

如果我是你。我制作了一个独特的“umembers”表。

我用触发器填充它。（members1 和 members2 也是）您可以找出更新触发器。

CREATE TRIGGER IF NOT EXISTS members1trigger
BEFORE INSERT ON members1 
BEGIN
    INSERT INTO umembers VALUES(new.uname, other properties);
END;

那么你的更新太棒了

#!/usr/bin/env bash
sqlite3 test.db <<EOF

update members1 set uname=$2
where uname='$1' and not exists (select 1 from umembers where uname= $2);

EOF

  1. 当然，您应该在 umembers 上创建唯一约束。如果有并行更新，那么第二个可能会抛出异常。但我认为索引锁定它，并且不会发生。

  2. 安全性表示使用不区分大小写的登录。

https://security.stackexchange.com/questions/241701/case-sensitive-logins

推荐阅读