SQL 在 SQLite 中声明变量并使用它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7739444/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Declare variable in SQLite and use it
提问by Muhammad Nour
I want to declare a variable in SQLite and use it in insertoperation.
我想在 SQLite 中声明一个变量并在insert操作中使用它。
Like in MS SQL:
就像在 MS SQL 中一样：
declare @name as varchar(10)
set name = 'name'
select * from table where name = @name
For example, I will need to get last_insert_rowand use it in insert.
例如，我需要last_insert_row在insert.
I have found something about binding but I didn't really fully understood it.
我发现了一些关于绑定的东西，但我并没有真正完全理解它。
回答by Herman Schoenfeld
SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.
SQLite 不支持本机变量语法，但您可以使用内存中的临时表实现几乎相同的效果。
I've used the below approach for large projects and works like a charm.
我已经将以下方法用于大型项目，并且效果很好。
/* Create in-memory temp table for variables */
BEGIN;
PRAGMA temp_store = 2;
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;
回答by stenci
Herman's solution works, but it can be simplified because Sqlite allows to store any value type on any field.
Herman 的解决方案有效，但可以简化，因为 Sqlite 允许在任何字段上存储任何值类型。
Here is a simpler version that uses one Valuefield declared as TEXTto store any value:
这是一个更简单的版本，它使用一个Value声明为TEXT存储任何值的字段：
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';
回答by DenverCR
For a read-only variable (that is, a constant value set once and used anywhere in the query), use a Common Table Expression (CTE).
对于只读变量（即设置一次并在查询中的任何位置使用的常量值），请使用公共表表达式 (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
回答by ThisClark
Herman's solution worked for me, but the ...had me mixed up for a bit. I'm including the demo I worked up based on his answer. The additional features in my answer include foreign key support, auto incrementing keys, and use of the last_insert_rowid()function to get the last auto generated key in a transaction.
赫尔曼的解决方案对我有用，但...让我有点困惑。我包括我根据他的回答制作的演示。我的答案中的附加功能包括外键支持、自动递增键以及使用该last_insert_rowid()函数获取事务中最后一个自动生成的键。
My need for this information came up when I hit a transaction that required three foreign keys but I could only get the last one with 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;
回答by Unfamiliar
Try using Binding Values. You cannot use variables as you do in T-SQL but you can use "parameters". I hope the following link is usefull.Binding Values
尝试使用绑定值。您不能像在 T-SQL 中那样使用变量，但可以使用“参数”。我希望以下链接有用。绑定值
