天道酬勤,学无止境

如何在 SQLite 中创建不区分大小写的唯一列(How to make a case-insensitive unique column in SQLite)

问题

我一直无法找到答案。 我正在尝试创建一个具有唯一电子邮件地址列的表。 当我这样做时

CREATE TABLE users (
  email TEXT PRIMARY KEY,
  password TEXT NOT NULL CHECK(password<>''),
  UNIQUE (lower(email))
)

使用 PDO 时,出现错误:

致命错误:未捕获的异常 'PDOException' 带有消息 'SQLSTATE[HY000]:一般错误:1 靠近“(”:script.php 中的语法错误:65 堆栈跟踪:#0 script.php(65):PDO->exec ('CREATE TABLE us...') #1 {main} 在第 65 行的 script.php 中抛出

第 65 行是CREATE TABLE行。 如果我取出UNIQUE ,它工作正常。 有更好的方法吗?

回答1

COLLATE NOCASE 是您的朋友:

CREATE TABLE users (
  email TEXT PRIMARY KEY,
  password TEXT NOT NULL CHECK(password<>''),
  UNIQUE (email COLLATE NOCASE)
)
回答2

为了速度,首先将所有输入小写,并使用普通的唯一列。

这对于读取比写入更频繁的用例非常有用,因为查询只需要比较字符串,而不是首先转换必须进行多次比较的字段。

标签

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • SQLAlchemy中不区分大小写的字符串列?(Case insensitive string columns in SQLAlchemy?)
    问题 我可以在sqlalchemy中创建不区分大小写的字符串列吗? 即时通讯使用的SQLite,并有可能通过更改排序规则通过数据库来做到这一点,但我想将其保留在sqlalchemy / python中。 回答1 SQLite允许在文本字段上使用NOCASE排序规则: SQLite version 3.6.22 sqlite> create table me (name text collate nocase); sqlite> .schema CREATE TABLE me (name text collate nocase); sqlite> insert into me values("Bob"); sqlite> insert into me values("alice"); sqlite> select * from me order by name; alice Bob SQLalchemy在模式上有一个collat​​ion()运算符,但是我不确定何时应用它。 回答2 在SQLAlchemy 0.8中,他们向所有String类型添加了归类参数。 现在,几个数据库后端(包括MySQL,SQLite和Postgresql)都支持COLLATE关键字。 您应该能够编写如下内容: my_table = Table('table_name', meta, Column('my
  • 忽略重复项并在Excel中创建唯一值的新列表(Ignore Duplicates and Create New List of Unique Values in Excel)
    问题 我有一列值经常显示为重复项。 我需要根据第一列创建一个具有唯一值的新列,如下所示: Column A Column B a a a b b c c c B列实际上将需要出现在同一工作簿中的不同工作表上,因此我认为它将需要使用sheet2!A1样式格式。 我对Data / Filter菜单选项没有任何运气,因为这似乎只能在命令下起作用。 每当在A列中输入新值时,我都需要B列自动更新。 回答1 Totero的答案是正确的。 该链接也非常有帮助。 基本上,您需要的公式是: B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) 然后按ctrl + shift + enter (否则将无法使用数组公式)。 这里要记住两件重要的事情:完整列表位于单元格A2:A20 ,然后必须将此公式粘贴到单元格B2 (不是B1因为这将为您提供循环引用)。 其次,这是一个数组公式,因此您需要按ctrl + shift + enter否则将无法正常工作。 回答2 这里有一个很好的指导。 基本上类似于: =INDEX(Sheet1!$A$1:$A$20, MATCH(0, COUNTIF($B$1:B1,Sheet!$A$1:$A$20), 0)) 回答3 在我的情况下,当使用以下公式时,excel被冻结 B2 = INDEX($ A
  • 字符串比较时如何将Sqlite3设置为不区分大小写?(How to set Sqlite3 to be case insensitive when string comparing?)
    问题 我想通过字符串匹配从sqlite3数据库中选择记录。 但是,如果我在where子句中使用'=',我发现sqlite3区分大小写。 谁能告诉我如何使用不区分大小写的字符串比较? 回答1 您可以在SELECT查询中使用COLLATE NOCASE : SELECT * FROM ... WHERE name = 'someone' COLLATE NOCASE 另外,在SQLite中,您可以通过在列定义中指定collate nocase (其他选项为binary (默认)和rtrim ;请参见此处)来指示在创建表时该列不区分大小写。 您也可以在创建索引时指定不collate nocase 。 例如: create table Test ( Text_Value text collate nocase ); insert into Test values ('A'); insert into Test values ('b'); insert into Test values ('C'); create index Test_Text_Value_Index on Test (Text_Value collate nocase); 现在,涉及Test.Text_Value表达式应该不区分大小写。 例如: sqlite> select Text_Value from Test
  • 如何在Swift中创建不可变数组?(How do you create an immutable array in Swift?)
    问题 如何在Swift中创建不可变数组? 简要阅读文档会建议您可以做 let myArray = [1,2,3] 但是可悲的是,这实际上产生了一个可变的,固定大小的数组。 这种可变性通常会产生令人困惑的别名,它们会产生意想不到的别名,并且会改变其参数: let outterArray = [myArray, myArray] outterArray[0][0] = 2000 outterArray //=> [[2000,2,3],[2000,2,3]] surprise! func notReallyPure(arr:Int[]) -> () { arr[0] = 3000 } notReallyPure(myArray) myArray // => [3000,2,3] 没有比C好多少了。 如果我想要不变性,那么将它包裹在NSArray的最佳选择就是这样: let immutableArray = NSArray(myArray: [1,2,3]) 好像疯了。 我在这里想念什么? 更新(2015-07-26): 这个问题可以追溯到Swift的早期。 从那以后,对Swift进行了更新,以使不可变数组实际上是不可变的,如下面的答案所示。 回答1 Xcode 6 beta 3改变了这种情况。正如您所描述的,过去数组是半可变的,它们的元素是可变的,但是长度是固定的
  • 如何在reStructuredText中创建不间断的空格?(How can I make a non-breaking space in reStructuredText?)
    问题 如何在reStructuredText中创建不间断的空格? 一个明显但有问题的解决方案是: `word A` 但是,对于不同的实现,例如rst2latex或rst2pdf,可能会有所不同。 另外,它以斜体显示。 回答1 我在运行docutils v0.9时没有看到问题。 至少rst2latex和rst2html关于不间断空格的行为正确。 Latex生成〜,而html生成&nbsp; 输入不间断字符(\ xa0,\ 0240)时。 也许您有编辑问题? 如果您可以设法输入字符,则docutils将完成此工作。 回答2 您需要unicode指令,但只能在替换中使用。 因此,您需要定义这样的替换: .. |nbsp| unicode:: 0xA0 :trim: 然后像这样使用它: xx |nbsp| xx :trim:是否有替代替换周围的空白的地方。 回答3 您也可以使用|_| 代替|nbsp| 鉴于reStructuredText的目标是将其可读为纯文本,因此在视觉上没有那么麻烦。 回答4 我最终想到了Sphinx的解决方法。 我覆盖了HTML和LaTeX编写器,将~字符转换为不间断的空格。 这是HTML之一: import sphinx.writers.html BaseTranslator = sphinx.writers.html
  • 如何更改sqlite3数据库的排序规则以区分大小写?(How to change the collation of sqlite3 database to sort case insensitively?)
    问题 我有一个查询sqlite3数据库,该数据库提供了已排序的数据。 数据是根据列( varchar列“名称”)进行排序的。 现在当我执行查询时 select * from tableNames Order by Name; 它提供了这样的数据。 Pen Stapler pencil 表示正在考虑区分大小写的内容。 我想要的方式如下 Pen pencil Stapler 那么,对于必要的结果,我应该在sqlite3数据库中进行哪些更改? 相关如何在字符串比较时将Sqlite3设置为不区分大小写? 回答1 SQLite数据类型文档讨论了用户定义的排序规则序列。 具体来说,您可以使用COLLATE NOCASE实现您的目标。 他们举了一个例子: CREATE TABLE t1( a, -- default collation type BINARY b COLLATE BINARY, -- default collation type BINARY c COLLATE REVERSE, -- default collation type REVERSE d COLLATE NOCASE -- default collation type NOCASE ); 并注意: -使用NOCASE排序规则序列进行分组(即,值-'abc'和'ABC'放在同一组中)。 SELECT count(*
  • 实体框架:如何避免表中的“鉴别符”列?(Entity Framework: How to avoid Discriminator column from table?)
    问题 我使用实体框架代码优先方法创建了下表。 如何修改C#代码,以便不在数据库中创建不需要的Discriminator列? 有什么属性可以实现这一目标? 如何使外键列命名为PaymentID而不是Payment_ PaymentID ? 有什么属性可以实现这一目标? 注意:EntityFramework.dll的运行时版本为v4.0.30XXX 。 代码 public abstract class PaymentComponent { public int PaymentComponentID { get; set; } public int MyValue { get; set; } public string MyType { get; set; } public abstract int GetEffectiveValue(); } public partial class GiftCouponPayment : PaymentComponent { public override int GetEffectiveValue() { if (MyValue < 2000) { return 0; } return MyValue; } } public partial class ClubCardPayment : PaymentComponent { public
  • How do I check in SQLite whether a table exists?(How do I check in SQLite whether a table exists?)
    问题 我如何可靠地在SQLite中检查特定的用户表是否存在? 我不是在要求不可靠的方法,例如检查表上的“ select *”是否返回错误(这甚至是个好主意吗?)。 原因是这样的: 在我的程序中,我需要创建然后填充一些表(如果它们尚不存在)。 如果它们确实已经存在,则需要更新一些表。 我是否应该采取其他路径来表示已经建立了相关表,例如,通过在磁盘上的程序初始化/设置文件中创建/放置/设置某个标志,还是其他方法? 还是我的方法有意义? 回答1 I missed that FAQ entry. Anyway, for future reference, the complete query is: SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}'; Where {table_name} is the name of the table to check. Documentation section for reference: Database File Format. 2.6. Storage Of The SQL Database Schema This will return a list of tables with the name specified; that is, the
  • SQLite中的主键是否需要索引?(Is an index needed for a primary key in SQLite?)
    问题 当将整数列标记为SQLite表中的主键时,是否也应该为其显式创建索引? SQLite似乎不会自动为主键列创建索引,但是给定目的,无论如何它还是会为其建立索引? (我将一直在该列上进行搜索)。 字符串主键的情况会有所不同吗? 回答1 它为你做。 除了INTEGER PRIMARY KEY列外,UNIQUE和PRIMARY KEY约束都通过在数据库中创建索引来实现(与“ CREATE UNIQUE INDEX”语句的方式相同)。 像数据库中的任何其他索引一样,使用这种索引来优化查询。 结果,在已经集体受到UNIQUE或PRIMARY KEY约束的一组列上创建索引时,通常没有任何优势(但是开销很大)。 回答2 如果将列标记为INTEGER PRIMARY KEY,则实际上它的速度大约是通过指定任何其他PRIMARY KEY或索引值进行的类似搜索的两倍。 这是因为: ... SQLite表中的所有行都有一个64位带符号整数键,可唯一标识其表中的行...搜索具有特定rowid的记录,或搜索具有指定范围内的rowid的所有记录的时间大约是原来的两倍。与通过指定其他任何PRIMARY KEY或索引值进行的类似搜索一样快。 除了下面提到的一个例外,如果rowid表的主键由单个列组成,并且该列的声明类型为大写和小写的“ INTEGER” ,则该列将成为rowid的别名。 这样的列通常称为
  • 自动在Ansible中创建不存在的目录的简单方法是什么(What's the easy way to auto create non existing dir in ansible)
    问题 在我的Ansible剧本中,我需要多次在其中创建文件 - name: Copy file template: src: code.conf.j2 dest: "{{project_root}}/conf/code.conf" 现在很多时候conf dir不存在。 然后,我必须创建更多任务以首先创建该目录。 如果不存在某些选项,有什么简单的方法可以自动创建目录 回答1 现在,这是唯一的方法 - name: Ensures {{project_root}}/conf dir exists file: path={{project_root}}/conf state=directory - name: Copy file template: src: code.conf.j2 dest: "{{project_root}}/conf/code.conf" 回答2 为确保使用完整路径成功,请使用recurse = yes - name: ensure custom facts directory exists file: > path=/etc/ansible/facts.d recurse=yes state=directory 回答3 如果您正在运行Ansible> = 2.0,则还有dirname过滤器,可用于提取路径的目录部分。 这样,您可以只使用一个变量来保存整个路径
  • 我可以在 SQLite 中创建计算列吗?(Can I create computed columns in SQLite?)
    问题 例如,创建一个名为Car_Model的表的语法是什么(如果可能),该表具有一个表Car_Make的外键,并为Car_Make一个列,该列是该Car_Models存在的Car_Make 。 (如果这看起来微不足道或像家庭作业一样,那是因为我只是在家里玩一些 python 试图重现我在工作中遇到的问题。我们在工作中使用 MS-SQL。) 回答1 更新:从3.31.0版(于 2020 年 1 月 22 日发布)开始,SQLite 支持计算列,因此以下答案适用于 3.31.0 之前的版本 SQLite 不支持计算列。 然而,您的问题可以通过一个相对简单的 SQL 查询来解决,然后您可以创建一个视图,使其看起来像一个带有额外计算列的表。 SELECT Car_Make.Name, Count(*) AS Count FROM Car_Make, Car_Model WHERE Car_Make.Id = Car_Model.Make GROUP BY Car_Make.Name 这应该返回一个类似于以下的表 Name Count ---- ----- Nissan 5 Toyota 20 Ford 10 回答2 我可以在 SQLite 中创建计算列吗? 是的,有可能。 此功能于 2020-01-22 - SQLite 3.31.0 添加 生成的列生成的列(有时也称为“计算列”
  • 如何在具有唯一 ID 的 SQLite 中插入重复行?(How to insert duplicate rows in SQLite with a unique ID?)
    问题 这看起来很简单:我想在 SQLite 表中复制一行: INSERT INTO table SELECT * FROM table WHERE rowId=5; 如果没有明确的唯一列声明,该语句将起作用,但表的第一列声明为rowID INTEGER NOT NULL PRIMARY KEY 。 有没有什么方法可以创建一个像上面那样的简单语句,在不知道表的模式(除了第一列)的情况下工作? 回答1 这可以使用 * 语法完成,而无需知道表的架构(除了主键的名称)。 诀窍是使用“CREATE TABLE AS”语法创建一个临时表。 在这个例子中,我假设有一个现有的、填充的、名为“src”的表,带有一个名为“id”的整数主键,以及其他几个列。 要复制“src”的行,请在 SQLite3 中使用以下 SQL: CREATE TEMPORARY TABLE tmp AS SELECT * FROM src; UPDATE tmp SET id = NULL; INSERT INTO src SELECT * FROM tmp; DROP TABLE tmp; 上面的例子复制了表“src”的所有行。 要仅复制所需的行,只需在第一行添加 WHERE 子句即可。 此示例有效,因为表“tmp”没有主键约束,但“src”有。 将 NULL 主键插入 src 会导致它们被赋予自动生成的值。 从
  • 如何处理iOS SQLite中的重音字符?(How to deal with accented characters in iOS SQLite?)
    问题 我需要执行对大小写和重音不敏感的SELECT查询。 出于演示目的,我创建了一个这样的表: create table table ( column text collate nocase ); insert into table values ('A'); insert into table values ('a'); insert into table values ('Á'); insert into table values ('á'); create index table_cloumn_Index on table (column collate nocase); 然后,在执行以下查询时得到这些结果: SELECT * FROM table WHERE column LIKE 'a'; > A > a SELECT * FROM table WHERE column LIKE 'á'; > á SELECT * FROM table WHERE column LIKE 'Á'; > Á 我该如何解决,以便以下任何查询的结果都像这样: > A > a > Á > á 顺便说一下,sqlite在iOS上运行。 提前致谢, 回答1 两种基本方法: 您可以在表中创建第二列,其中包含不带国际字符的字符串。 此外,在对该辅助搜索列进行搜索之前
  • 嵌入式数据库sqlite3【基础篇】-基本命令操作,小白一看就懂
    前言数据在实际工作中应用非常广泛,数据库的产品也比较多,oracle、DB2、SQL2000、mySQL;基于嵌入式linux的数据库主要有SQLite, Firebird, Berkeley DB, eXtremeDB。本文主要讲解数据库SQLite,通过这个开源的小型的嵌入式数据库带领大家掌握一些基本的数据库操作,这些操作在很多系统中都是通用的,可谓学一通百。SQLite SQLite,是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。作者D.RichardHipp2000年1月,Hipp开始和一个同事讨论关于创建一个简单的嵌入式SQL数据库的想法,这个数据库将使用GNU DBM哈希库(gdbm)做后台,同时这个数据库将不需要安装和管理支持。后来,一有空闲时间,Hipp就开始实施这项工作,2000年8月,SQLite 1.0版发布了。【在此向大神献上我的膝盖!】它的设计目标是嵌入式的,而且已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如 Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲,它的处理速度比他们都快
  • 在SQLite中进行重音搜索(android)(Accented Search in sqlite (android))
    问题 我有一列,其中某些元素包含重音字母。 例如:Grambú 我的要求是,当我搜索“ Grambu”时,结果中也应显示“Grambú”。 对于此要求,我尝试为该特定列使用“ COLLATE NOCASE”参数。 但这没有用。 当我在网络上搜索解决方案时,我发现很多人建议对重音字符进行规范化,并以此为基础创建另一列作为唯一选择。 还有其他更简单的解决方案来解决这个问题吗? 回答1 COLLATE NOCASE仅适用于ASCII的26个大写字符。 使用setLocale()并使用COLLATE LOCALIZED将数据库的语言环境设置为具有突出字符支持的语言环境。 您也可以尝试使用COLLATE UNICODE。 但是请注意以下错误:ICS中的SQLite UNICODE排序已损坏-不再区分大小写。 查看说明文件,以提及Android中的这两个整理器。 另外,请查看此在线整理演示工具。 回答2 http://www.sqlite.org/lang_expr.html (一个错误:默认情况下,SQLite仅理解ASCII字符的大写/小写。默认情况下,LIKE运算符对ASCII范围之外的Unicode字符区分大小写。例如,表达式'a'LIKE'A'为TRUE,但'æ'就像'Æ'是FALSE。) 回答3 在Android sqlite中, LIKE和GLOB会同时忽略COLLATE
  • 如何从sqlite查询中获取字典?(How can I get dict from sqlite query?)
    问题 db = sqlite.connect("test.sqlite") res = db.execute("select * from table") 通过迭代,我得到了对应于行的列表。 for row in res: print row 我可以得到列的名称 col_name_list = [tuple[0] for tuple in res.description] 但是是否有一些功能或设置可以获取字典而不是列表? {'col1': 'value', 'col2': 'value'} 还是我必须自己做? 回答1 您可以使用row_factory,如docs中的示例所示: import sqlite3 def dict_factory(cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d con = sqlite3.connect(":memory:") con.row_factory = dict_factory cur = con.cursor() cur.execute("select 1 as a") print cur.fetchone()["a"] 或按照文档中此示例之后给出的建议进行操作: 如果返回一个元组还不够
  • 如何在Java中创建不可变列表?(How to create Immutable List in java?)
    问题 我需要将可变列表对象转换为不可变列表。 java中可能的方法是什么? public void action() { List<MutableClass> mutableList = Arrays.asList(new MutableClass("san", "UK", 21), new MutableClass("peter", "US", 34)); List<MutableClass> immutableList = immutateList(mutableList); } public List<MutableClass> immutateList(List<MutableClass> mutableList){ //some code here to make this beanList immutable //ie. objects and size of beanList should not be change. //ie. we cant add new object here. //ie. we cant remove or change existing one. } MutableClass final class MutableClass { final String name; final String address; final int age
  • 如何在python中创建不可变的字典?(How to create an immutable dictionary in python?)
    问题 我想subclass dict在Python这样子类的所有词典是不可改变的。 我不理解__hash__如何影响不变性,因为在我的理解中,它仅表示对象的相等或不相等! 因此,可以使用__hash__来实现不变性吗? 如何 ? 更新: 目的是可以将来自API的常见响应作为字典使用,必须将其作为全局变量共享。 因此,无论什么情况都需要完整无缺。 回答1 我找到了官方参考资料: class imdict(dict): def __hash__(self): return id(self) def _immutable(self, *args, **kws): raise TypeError('object is immutable') __setitem__ = _immutable __delitem__ = _immutable clear = _immutable update = _immutable setdefault = _immutable pop = _immutable popitem = _immutable 出处:http://www.python.org/dev/peps/pep-0351/ 回答2 因此,可以使用__hash__实现不变性吗? 不,不能。 无论对象的__hash__方法做什么,都可以使其可变(或不可变)。 不变对象与__hash_
  • 如何使我的postgresql数据库使用不区分大小写的排序规则?(How to make my postgresql database use a case insensitive collation?)
    问题 在几个SO帖子中,OP要求以一种不区分大小写的方式搜索文本列的有效方法。 据我所知,最有效的方法是拥有一个不区分大小写的排序规则的数据库。 就我而言,我是从头开始创建数据库的,所以我对DB排序规则具有完美的控制。 唯一的问题是我不知道如何定义它,也找不到任何示例。 请告诉我如何创建不区分大小写的排序规则的数据库。 我正在使用PostgreSQL 9.2.4。 编辑1 CITEXT扩展是一个很好的解决方案。 但是,它有一些限制,如文档中所述。 如果没有更好的方法,我一定会使用它。 我要强调的是,我希望所有字符串操作均不区分大小写。 对每个TEXT字段使用CITEXT是一种方法。 但是,如果可能的话,最好使用不区分大小写的排序规则。 现在,https://stackoverflow.com/users/562459/mike-sherrill-catcall表示PostgreSQL使用底层系统公开的任何排序规则。 我不介意使操作系统公开不区分大小写的排序规则。 唯一的问题我不知道该怎么做。 回答1 自从这个问题以来,发生了很多变化。 PostgreSQL v12中增加了对不区分大小写排序规则的本机支持。 如其他答案中所述,这基本上不赞成citext扩展名。 在PostgreSQL v12中,可以执行以下操作: CREATE COLLATION case_insensitive (
  • 如何在sqlite ios中已制成的表中添加唯一约束?(How to add unique constraint in already made table in sqlite ios?)
    问题 我现在已经通过sqlite数据库浏览器制作了数据库和表,因为我允许从Facebook向用户添加选定的朋友名称,但是我不希望第二次在他们添加名称时不应该在数据库中添加相同的名称,因此如何在sqlite中添加约束 我试图检查sqlite数据库浏览器,但是没有什么可更改的 我为它尝试了lita,但是在lita中有使其不为null和唯一的选项,但是我无法单击该复选框,我不知道为什么 请帮助 回答1 我假设您正在使用FireFox的SQLiteManager,请再次创建该表,通常,当您已经创建表后,它不允许更改约束。 注意-也可以使用代码来完成,这是另一种方式。 编辑见下图 回答2 您不能在SQLite中向现有表添加约束(在SQL中,可以选择执行此操作)。 您需要重新创建带有必要约束的表。 在sqlite中,只有几个选项可用于alter table命令。 请检查图片: 还要检查Sqlite组织参考。 编辑 但是,您可以为表添加唯一索引以达到相同的效果。 因此,您可以使用以下查询来实现: CREATE UNIQUE INDEX your_unique_index ON your_table(column_name); 在大多数情况下,通过在数据库中创建唯一索引来实现UNIQUE和PRIMARY KEY约束。 参考:SQLite约束 回答3 您可以通过创建唯一索引来添加约束: CREATE