在 SQLite 中声明变量并使用它

Question

我想在 SQLite 中声明一个变量并在 insert 操作中使用它。

就像在 MS SQL 中一样：

declare @name as varchar(10)
set name = 'name'
select * from table where name = @name

例如，我需要获取 last_insert_row 并在 insert 中使用它。

我发现了一些关于绑定的东西，但我并没有真正完全理解它。

Answer 1

SQLite 不支持本机变量语法，但您可以使用内存临时表实现几乎相同的效果。

我在大型项目中使用了以下方法，效果非常好。

    /* Create in-memory temp table for variables */
    BEGIN;

    PRAGMA temp_store = 2; /* 2 means use in-memory */
    CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);

    /* Declaring a variable */
    INSERT INTO _Variables (Name) VALUES ('VariableName');

    /* Assigning a variable (pick the right storage class) */
    UPDATE _Variables SET IntegerValue = ... WHERE Name = 'VariableName';

    /* Getting variable value (use within expression) */
    ... (SELECT coalesce(RealValue, IntegerValue, BlobValue, TextValue) FROM _Variables WHERE Name = 'VariableName' LIMIT 1) ...

    DROP TABLE _Variables;
    END;

Answer 2

对于只读变量（即设置一次并在查询中的任何位置使用的常量值），请使用公共表表达式 (CTE)。

WITH const AS (SELECT 'name' AS name, 10 AS more)
SELECT table.cost, (table.cost + const.more) AS newCost
FROM table, const 
WHERE table.name = const.name

SQLite WITH 子句

Answer 3

Herman 的解决方案有效，但可以简化，因为 Sqlite 允许在任何字段上存储任何值类型。

下面是一个更简单的版本，它使用一个声明为 TEXT 的 Value 字段来存储任何值：

CREATE TEMP TABLE IF NOT EXISTS Variables (Name TEXT PRIMARY KEY, Value TEXT);

INSERT OR REPLACE INTO Variables VALUES ('VarStr', 'Val1');
INSERT OR REPLACE INTO Variables VALUES ('VarInt', 123);
INSERT OR REPLACE INTO Variables VALUES ('VarBlob', x'12345678');

SELECT Value
  FROM Variables
 WHERE Name = 'VarStr'
UNION ALL
SELECT Value
  FROM Variables
 WHERE Name = 'VarInt'
UNION ALL
SELECT Value
  FROM Variables
 WHERE Name = 'VarBlob';

Answer 4

在您的示例中使用 denverCR 的答案：

WITH tblCTE AS (SELECT "Joe" AS namevar)
SELECT * FROM table, tblCTE
WHERE name = namevar

作为初学者，我发现其他答案太难以理解，希望这有效

Answer 5

赫尔曼的解决方案对我有用，但 ... 让我有点困惑。我包括了我根据他的回答制作的演示。我的答案中的附加功能包括外键支持、自动递增键以及使用 last_insert_rowid() 函数获取事务中最后一个自动生成的键。

当我遇到需要三个外键的事务但我只能使用 last_insert_rowid() 获取最后一个外键时，我就需要此信息。

PRAGMA foreign_keys = ON;   -- sqlite foreign key support is off by default
PRAGMA temp_store = 2;      -- store temp table in memory, not on disk

CREATE TABLE Foo(
    Thing1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

CREATE TABLE Bar(
    Thing2 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    FOREIGN KEY(Thing2) REFERENCES Foo(Thing1)
);

BEGIN TRANSACTION;

CREATE TEMP TABLE _Variables(Key TEXT, Value INTEGER);

INSERT INTO Foo(Thing1)
VALUES(2);

INSERT INTO _Variables(Key, Value)
VALUES('FooThing', last_insert_rowid());

INSERT INTO Bar(Thing2)
VALUES((SELECT Value FROM _Variables WHERE Key = 'FooThing'));

DROP TABLE _Variables;

END TRANSACTION;

Answer 6

创建“VARIABLE”以在 SQLite SELECT（和其他一些）语句中使用

CREATE TEMP TABLE IF NOT EXISTS variable AS SELECT '2002' AS _year; --creating the "variable" named "_year" with value "2002"
UPDATE variable SET _year = '2021'; --changing the variable named "_year" assigning "new" value "2021"
SELECT _year FROM variable; --viewing the variable
SELECT 'TEST', (SELECT _year FROM variable) AS _year; --using the variable
SELECT taxyr FROM owndat WHERE taxyr = (SELECT _year FROM variable); --another example of using the variable
SELECT DISTINCT taxyr FROM owndat WHERE taxyr IN ('2022',(SELECT _year FROM variable)); --another example of using the variable
DROP TABLE IF EXISTS variable; --releasing the "variable" if needed to be released

Answer 7

阅读完所有答案后，我更喜欢这样的内容：

select *
from table, (select 'name' as name) const
where table.name = const.name

Answer 8

我经常需要对数据库中的案例进行事后分析。这涉及到运行一系列针对该组值进行过滤的查询，每次使用一组不同的值。

我发现拥有一个“变量表”非常有用，每个“变量”需要一列，我每次都设置我需要的值，并且一系列查询不会每次都改变。

PRAGMA temp_store = 2;      -- store temp table in memory, not on disk
CREATE TEMP TABLE IF NOT EXISTS _vars (_var1 INTEGER, _var2 INTEGER);

DELETE FROM _vars WHERE TRUE;       -- run this when changing set of values
INSERT OR REPLACE INTO  _vars VALUES (1492, 1112);       --new values
SELECT * FROM _vars;       -- check values are set and only one row is present

-- here comes the series of queries I need to run for each set of values
SELECT * 
FROM my_table_name  
WHERE one_column_name = (SELECT _var1 FROM _vars)
  AND other_column_name = (SELECT _var2 FROM _vars);

如果您确保您的“变量”具有唯一的名称（该名称不会出现在您正在查询的表中），您可以进一步简化查询：

SELECT * 
FROM my_table_name, _vars
WHERE one_column_name = _var1
  AND other_column_name = _var2;

当我完成一组值时，我将返回到DELETE 并为我的“变量”设置一组新值并继续。

最后，通过

DROP TABLE IF EXISTS _vars;

警告

进行清理。请记住，所有这些都是在 PRAGMA temp_store = 2; 生效时进行的，因此任何数据创建都将尝试在内存中进行。

还要注意数据库/连接的更改。在内存中创建的表与活动数据库或连接耦合。如果您更改数据库（认为“USE”）或在数据库编辑器/GUI 中更改连接，内存中的时态表将不会遵循：您将必须为下一个数据库或连接再次创建它们。这意味着，如果您要更改数据库以比较来自多个数据库的查询结果，请记住您的查询正在使用内存中当前关联的表中的值与您正在使用的数据库。因此，如果您将它们更改为在一个数据库中运行查询，然后更改为在另一个数据库中运行相同的查询，则需要在运行查询之前更改内存中的值，否则将不会运行相同的查询。

Answer 9

尝试使用绑定值。您不能像在 T-SQL 中那样使用变量，但可以使用“参数”。我希望以下链接有用。绑定值

Answer 10

我找到了一种将变量分配给 COLUMN 或 TABLE 的解决方案：

conn = sqlite3.connect('database.db')
cursor=conn.cursor()
z="Cash_payers"   # bring results from Table 1 , Column: Customers and COLUMN 
# which are pays cash
sorgu_y= Customers #Column name
query1="SELECT  * FROM  Table_1 WHERE " +sorgu_y+ " LIKE ? "
print (query1)
query=(query1)
cursor.execute(query,(z,))

不要忘记在 WHERE 和双引号之间输入一个空格
以及双引号和 LIKE 之间