天道酬勤,学无止境

How to make a case-insensitive unique column in SQLite

I haven't been able to find the answer to this. I'm trying to create a table with a unique email address column. And when I do

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

when using PDO, I get the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 near "(": syntax error' in script.php:65 Stack trace: #0 script.php(65): PDO->exec('CREATE TABLE us...') #1 {main} thrown in script.php on line 65

Line 65 is the CREATE TABLE line. If I take out the UNIQUE, it works fine. Is there a better way of doing it?

标签

评论

COLLATE NOCASE is your friend:

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

For speed, make all your input lower case first, and use a normal unique column.

This is great for read-more-frequent-than-write use cases because queries just need to compare strings, rather than converting the field first which it must do lots of times to compare.

受限制的 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>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • Rails模型中不区分大小写的搜索(Case-insensitive search in Rails model)
    问题 我的产品型号包含一些物品 Product.first => #<Product id: 10, name: "Blue jeans" > 我现在要从另一个数据集中导入一些产品参数,但是名称的拼写不一致。 例如,在另一个数据集中,“ Blue jeans可以拼写为“ Blue Jeans 。 我想要Product.find_or_create_by_name("Blue Jeans") ,但这将创建一个新产品,几乎与第一个相同。 如果我想查找和比较小写的名字,该怎么办? 性能问题在这里并不是真正重要的:只有100-200种产品,我想将其作为导入数据的迁移来运行。 有任何想法吗? 回答1 您可能需要在这里更加详细 name = "Blue Jeans" model = Product.where('lower(name) = ?', name.downcase).first model ||= Product.create(:name => name) 回答2 这是Rails中的完整设置,仅供我参考。 如果它也对您有帮助,我感到很高兴。 查询: Product.where("lower(name) = ?", name.downcase).first 验证者: validates :name, presence: true, uniqueness: {case_sensitive
  • 字符串比较时如何将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
  • 如何为MySQL和Postgres编写不区分大小写的查询?(How do you write a case insensitive query for both MySQL and Postgres?)
    问题 我正在本地运行一个MySQL数据库以进行开发,但是正在部署到使用Postgres的Heroku。 Heroku处理几乎所有内容,但是不区分大小写的Like语句变得区分大小写。 我可以使用iLike语句,但是我的本地MySQL数据库无法处理该语句。 编写与MySQL和Postgres兼容的不区分大小写的查询的最佳方法是什么? 还是我需要根据与我的应用程序交谈的数据库编写单独的Like和iLike语句? 回答1 select * from foo where upper(bar) = upper(?); 如果在调用方中将参数设置为大写,则可以避免第二个函数调用。 回答2 这个故事的寓意是:不要使用不同的软件堆栈进行开发和生产。 绝不。 您将最终遇到无法在开发人员中重现的错误; 您的测试将一文不值。 只是不要这样做。 不可能使用其他数据库引擎-在更多情况下,它的行为将不只是LIKE(此外,您是否检查了数据库使用的排序规则?在每种情况下它们是否相同?如果不是,则可以忘记在varchar列上使用相同的ORDER BY) 回答3 使用Arel: Author.where(Author.arel_table[:name].matches("%foo%")) matches将对Postgres使用ILIKE运算符,对其他所有ILIKE使用LIKE 。 回答4 在postgres中
  • 可延迟,不区分大小写的唯一约束(Deferrable, case-insensitive unique constraint)
    问题 在PostgreSQL中是否可以在字符列上创建可延迟的唯一约束,但不区分大小写? 让我们假设下面的基本表: CREATE TABLE sample_table ( my_column VARCHAR(100) ); 如果不需要可延迟的约束,则就像使用函数创建唯一索引一样简单,例如: CREATE UNIQUE INDEX my_unique_index ON sample_table(UPPER(my_column)); 延迟约束检查需要显式创建约束,例如: ALTER TABLE sample_table ADD CONSTRAINT my_unique_constraint UNIQUE(my_column) DEFERRABLE INITIALLY IMMEDIATE; 不幸的是,不可能在唯一约束中使用任意函数。 一种可能的解决方法是创建与my_column内容相同的其他列,但大写形式在每次更新/插入后通过触发器进行更新,然后在此人工列上创建可延迟的唯一约束。 但是,这听起来确实很丑陋。 或者,应该可以使用CREATE CONSTRAINT TRIGGER并手动检查不区分大小写的唯一性(当然,仍然需要常规索引)。 对于这样一个简单的需求(我想很受欢迎),这听起来有些复杂。 有没有更简单和/或更优雅的方法来解决此限制? 回答1
  • Deferrable, case-insensitive unique constraint
    Is it possible in PostgreSQL to create a deferrable unique constraint on a character column, but case-insensitive? Let's assume the following basic table: CREATE TABLE sample_table ( my_column VARCHAR(100) ); If deferrable constraint is not needed, it is as simple as creating unique index with function, e.g.: CREATE UNIQUE INDEX my_unique_index ON sample_table(UPPER(my_column)); Deferred constraint check requires creating the constraint explicitly, e.g.: ALTER TABLE sample_table ADD CONSTRAINT my_unique_constraint UNIQUE(my_column) DEFERRABLE INITIALLY IMMEDIATE; And unfortunately it is not
  • 如何在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
  • 如何从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"] 或按照文档中此示例之后给出的建议进行操作: 如果返回一个元组还不够
  • Case-insensitive unique index in Rails/ActiveRecord?
    I need to create a case-insensitive index on a column in rails. I did this via SQL: execute( "CREATE UNIQUE INDEX index_users_on_lower_email_index ON users (lower(email))" ) This works great, but in my schema.rb file I have: add_index "users", [nil], :name => "index_users_on_lower_email_index", :unique => true Notice the "nil". So when I try to clone the database to run a test, I get an obvious error. Am I doing something wrong here? Is there some other convention that I should be using inside rails? Thanks for the help.
  • Django tests complain of missing tables
    When I run my test dealing with my Customer model, I get the following error: DatabaseError: (1146, "Table 'test_mcif2.customer' doesn't exist") I'm not entirely surprised because I have my Django project connected to a "legacy" database. Since my tables weren't created "the Django way," it's not shocking that Django wouldn't be able to talk to them without some finagling. Here's my model: from django.db import models from django.db import connection, transaction from mcif.models.mcif_model import McifModel class Customer(McifModel): class Meta: db_table = u'customer' app_name = 'mcif' id =
  • SQLite Unique Key with a combination of two columns
    I'm trying to make sure when I run the following query only the first INSERT INTO will work.. I know I have to make slot UNIQUE The slot could be from 0-5 INTEGER but it doesn't mean that only 6 table data rows could be accepted into that table. For each playerHash that matches it should only allow 6 table data rows as slot is UNIQUE (cannot have duplicate of same slot column, for each playerHash column). //Below Query Should Pass INSERT INTO Buying(itemId, amount, price, bought, slot, playerHash) VALUES (1, 1, 1, 1, 1, 1); //Below Query Should Fail INSERT INTO Buying(itemId, amount, price
  • SQLite表约束-在多列上是唯一的(SQLite table constraint - unique on multiple columns)
    问题 我可以在SQLite网站上找到语法“图表”,但没有示例,我的代码崩溃了。 我在单列上有其他具有唯一约束的表,但是我想在两列上向表添加约束。 这就是导致消息“语法错误”的SQLiteException的原因。 CREATE TABLE name (column defs) UNIQUE (col_name1, col_name2) ON CONFLICT REPLACE 我正在基于以下条件进行此操作: 表约束 为了清楚CONTSTRAINT name我提供的链接上的文档说CONTSTRAINT name应该在约束定义之前。 不过,可能导致解决方案的是调试器抱怨的是在括号列定义之后的内容。 如果我放 ...last_column_name last_col_datatype) CONSTRAINT ... 该错误在“ CONSTRAINT”附近:语法错误 如果我放 ...last_column_name last_col_datatype) UNIQUE ... 该错误在“ UNIQUE”附近:语法错误 回答1 将UNIQUE声明放在列定义部分中; 工作示例: CREATE TABLE a ( i INT, j INT, UNIQUE(i, j) ON CONFLICT REPLACE ); 回答2 好吧,您的语法与您包含的链接不匹配,该链接指定: CREATE TABLE
  • 在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中使用UUID(Using UUIDs in SQLite)
    问题 是否可以在SQLite中将UUID值用作主键? 我发现有关该主题的信息非常有限,因此我不确定SQLite是否甚至支持UUID数据类型。 我应该将UUID存储为字符串吗? 回答1 SQLite允许使用任何数据类型作为主键。 UUID可以存储为字符串(人类可读)或16字节BLOB(如果记录太小以至于差异很重要,则存储速度可能会更快)。 回答2 CL的答案是正确的,但是可以解决当前的问题。 如前所述,任何类型的一列(或多列)都可以用作主键。 因此,您可以将UUID以格式化的,人类可读的字符串格式存储,并使其成为表的键。 而且由于UUID只是一个128位整数,所以您也可以将整数的字节存储为BLOB,我想这样会更快一些。 但更直接地回答了我认为这个问题在手,不,SQLite没有直接支持UUID的任何功能。 当SQLite创建表时,它使用列的声明类型来确定将使用五个基础存储类(整数,实数,文本,blob或null)中的哪一个。 之后,不使用列的声明类型。 因此,没有特定于UUID的列类型或存储类。 似乎也没有任何可用于与格式化的UUID字符串之间进行转换的函数。 要获取UUID的字节,您需要查看应用程序编写语言所提供的方法。例如,Java的UUID类或Apple的NSUUID。 回答3 现在有一个sqlite扩展,可以根据https://sqlite.org/src/file/ext
  • Simulate ORDER BY in SQLite UPDATE to handle uniqueness constraint
    I have a table in SQLite 3 thus: sqlite> .schema CREATE TABLE table1 (id INTEGER PRIMARY KEY NOT NULL, title TEXT UNIQUE NOT NULL, priority INTEGER UNIQUE NOT NULL); Here is some sample data, for illustration: sqlite> SELECT * FROM table1; id title priority ---------- ---------- ---------- 1 a 1 2 b 2 3 c 3 4 d 4 I wish to add 1 to the priority of all cells with priority > 1. Here is my first attempt: sqlite> UPDATE table1 SET priority = priority + 1 WHERE priority > 1; Error: column priority is not unique This fails, presumably because the update is not ordered, allowing the UPDATE to attempt
  • how to set a column as unique indexer on Sqlite
    I have 3 columns (_id, column1, column2) _id column has been set as autoincrement In database there are some duplicate records, so I want to prevent duplicate records with setting column1 as unique indexer. How do I set a column as unique indexer on sqlite? Or how do I prevent duplicate records?
  • Laravel使用SQLite迁移'无法添加默认值NULL的NOT NULL列'(Laravel migration with SQLite 'Cannot add a NOT NULL column with default value NULL')
    问题 为什么在使用SQLite驱动程序时收到此警告? 我的MySQL驱动程序没有问题,但是SQLite抛出此错误。 这对我来说是没有意义的,因为我了解到所有迁移完成后都会发生播种,所以为什么它抱怨这个问题,只有在数据库中已经存在数据的情况下才会出现此问题。 我的两次迁移是 第一次迁移 public function up() { Schema::create('users', function($table) { $table->increments('id'); $table->string('username'); $table->string('email'); $table->string('password'); }); } 第二次迁移 public function up() { Schema::table('users', function(Blueprint $table) { $table->date('birthday')->after('id'); $table->string('last_name')->after('id'); $table->string('first_name')->after('id'); }); } 错误 Exception: SQLSTATE[HY000]: General error: 1 Cannot add a NOT
  • INT PRIMARY KEY和INTEGER PRIMARY KEY SQLite之间的区别(Difference between INT PRIMARY KEY and INTEGER PRIMARY KEY SQLite)
    问题 定义表的架构时, INT PRIMARY KEY和INTEGER PRIMARY KEY之间有什么区别吗? 当使用int主键时,我生成了sqlite_autoindex东西; 当整数主键时,我得到了sqlite_sequence表生成。 有什么不同? 第一个和第二个变体会产生哪些副作用? 回答1 是的,是有区别的: INTEGER是SQLite中一个特殊的情况下,当数据库中不单独创建一个主键,但重用的ROWID列来代替。 当您使用INT (或内部“映射”到INTEGER任何其他类型)时,将创建一个单独的主键。 这就是为什么您看到为INT主键创建了sqlite_autoindex ,而没有为INTEGER类型的索引创建索引的原因:SQLite对整数主键重复使用了内置索引结构,从而无需使用自动索引。 因此,无论是在存储方面还是在性能方面, INTEGER主键都更加经济。 有关详细信息,请参见此链接。 回答2 更新:SQLite的ROWID列现在是一个64位整数: 在SQLite中,类型为INTEGER PRIMARY KEY的列是ROWID的别名(在WITHOUT ROWID表中除外),该别名始终是64位有符号整数。 在SQLite 3文档中对所有内容进行了解释: 2.0整数主键 SQLite无类型性的一个例外是类型为INTEGER PRIMARY KEY的列。 (并且必须使用“
  • SQLite的NULL和唯一的? [关闭](Sqlite NULL and unique? [closed])
    问题 关门了。 这个问题需要细节或明确性。 它当前不接受答案。 想要改善这个问题吗? 添加详细信息,并通过编辑此帖子来澄清问题。 去年关闭。 改善这个问题 我注意到在具有UNIQUE约束的列中可以有NULL值: UNIQUE(col) 在某些情况下会产生任何问题吗? 回答1 尽管以下内容解决了多个空值,但除了可能的数据库/ SQL可移植性之外,它没有解决与这种设计相关的任何“问题”,因此,它可能不应该被视为答案,在此仅作参考。 这实际上在SQLite常见问题解答中有介绍。 这是一种设计选择-SQLite(与SQL Server不同)选择了多个NULL值不计入索引的唯一性。 SQL标准要求即使约束中的一个或多个列为NULL,也必须强制执行UNIQUE约束,但是SQLite不会这样做。 那不是错误吗? 也许您是指SQL92中的以下语句: 当且仅当表中没有两行在唯一列中具有相同的非空值时,才满足唯一约束。 该声明含糊不清,至少有两种可能的解释: 当且仅当表中没有两行具有相同的值且唯一列中具有非空值时,才满足唯一约束。 当且仅当表中没有两行在不为null的唯一列的子集中具有相同的值时,才满足唯一约束。 SQLite遵循解释(1),PostgreSQL,MySQL,Oracle和Firebird也是如此。 Informix和Microsoft SQL Server确实使用了解释(2)
  • how to set unique constraint over multiple column when any one can be null in sqlite
    How do I set unique constraint over multiple column when any one can be null in sqlite? e.g. I have made unique("col1","col2","col3") and tried with insert into tablename values("abc","def",null) twice it hase inserted both rows. The unique constraint is not working when third column is null.
  • 从PowerShell数组中删除重复的值(Removing duplicate values from a PowerShell array)
    问题 如何从PowerShell阵列中删除重复项? $a = @(1,2,3,4,5,5,6,7,8,9,0,0) 回答1 通过-Unique开关使用Select-Object(别名为select选择对象); 例如: $a = @(1,2,3,4,5,5,6,7,8,9,0,0) $a = $a | select -Unique 回答2 另一个选择是将Sort-Object(别名为sort ,但仅在Windows上)与 -Unique开关,将分类与删除重复项结合在一起: $a | sort -unique 回答3 如果您想完全防弹,这是我的建议: @('Apples', 'Apples ', 'APPLES', 'Banana') | Sort-Object -Property @{Expression={$_.Trim()}} -Unique 输出: Apples Banana 这将使用Property参数首先对字符串进行Trim() ,因此将删除多余的空格,然后仅选择-Unique值。 有关Sort-Object更多信息: Get-Help Sort-Object -ShowWindow 回答4 $a | sort -unique 这适用于不区分大小写的项目,因此可以删除大小写不同的重复字符串。 解决了我的问题。 $ServerList = @( "FS3", "HQ2",