Android 在 SQLite 表中使用文本作为主键不好吗？

Using text as a primary key in SQLite table bad?

提问by mpellegr

Is it bad to have text as a primary key in an SQLite database? I heard that it's bad for performance reasons, is this true? And will the rowid be used as the actual primary key in such a case?

将文本作为 SQLite 数据库中的主键是不是很糟糕？我听说出于性能原因这很糟糕，这是真的吗？在这种情况下，rowid 会被用作实际的主键吗？

采纳答案by Simon Dorociak

Is it bad to have text as a primary key in an SQLite database? I heard that it's bad for performance reasons, is this true?

将文本作为 SQLite 数据库中的主键是不是很糟糕？我听说出于性能原因这很糟糕，这是真的吗？

I never heard that somebody used string as primary keyin table. For me (and I honestly hope also for others) very "ugly" practisewith very low performance.

我从未听说有人使用字符串作为表中的主键。对我来说（老实说，我也希望其他人）非常“丑陋”的练习，性能非常低。

If you'll use string as primary key you needs to think about a "few" things:

如果您将使用字符串作为主键，您需要考虑“一些”事情：

• Will be combination of 3 symbols enough?
• Or should I use 5 symbols?

• 3个符号的组合就足够了吗？
• 还是我应该使用 5 个符号？

Here, each row must have same format(readability issue of course) and also be unique. Oh! Here is next "piggy work"->you'll need to create some "unique string generator"that will generate unique¹string identificator².

在这里，每一行必须具有相同的格式（当然是可读性问题）并且也是唯一的。哦！这是下一个“小猪工作”，->您需要创建一些“独特的字符串生成器”，它将生成唯一的¹字符串标识符²。

And also there are next issues is good to consider:

还有下一个问题值得考虑：

• Longer strings =automatically harder and harder to compare
• Size of table radically raises because it's pretty clear that string has much more size as number
• Number of rows - it's madness to use string as primary key if you table can have 1000+ rows

• =越长的字符串越难比较
• 表格的大小急剧增加，因为很明显字符串的大小比数字大得多
• 行数 - 如果你的表可以有 1000+ 行，那么使用字符串作为主键是很疯狂的

It's more complex theme but i would like to say that OK, for very small tables would be possible to use strings as primary key (if it makes a sence) but if you'll look at disadvantages it's much more better technique to use number as primary key for sure!

这是一个更复杂的主题，但我想说好的，对于非常小的表可以使用字符串作为主键（如果它有意义）但是如果你看看缺点，使用数字作为更好的技术主键是肯定的！

And what is conclusion?

什么是结论？

I don't recommend you to use string as primary key. It has more disadvantages as advantages (it has really some advantage?).

我不建议您使用字符串作为主键。它有更多的缺点作为优点（它真的有一些优点吗？）。

Usage of number as primary key is much more better (I'm scared to say the best) practise.

使用数字作为主键要好得多（我不敢说是最好的）实践。

And will the rowid be used as the actual primary key in such a case?

在这种情况下，rowid 会被用作实际的主键吗？

If you will use string as primary not.

如果您将使用字符串作为主要不。

¹_{In real strings are rarely unique.}

¹_{在实际字符串中很少是唯一的。}

²_{Of course, you could say that you can create identificator from name of item in row, but it's once again spaghetti code (items can have same name).}

²_{当然，您可以说您可以从行中的项目名称创建标识符，但这又是意大利面条式代码（项目可以具有相同的名称）。}

回答by laalto

Is it bad to have text as a primary key in an SQLite database? I heard that it's bad for performance reasons, is this true?

将文本作为 SQLite 数据库中的主键是不是很糟糕？我听说出于性能原因这很糟糕，这是真的吗？

From correctness point of view, TEXT PRIMARY KEYis all right.

从正确性来看，没问题TEXT PRIMARY KEY。

From performance point of view, prefer INTEGERkeys. But as with any performance issue, measure it yourself to see if there's a significant difference with your data and use cases.

从性能的角度来看，更喜欢INTEGER键。但与任何性能问题一样，请自行衡量以查看您的数据和用例是否存在显着差异。

And will the rowid be used as the actual primary key in such a case?

在这种情况下，rowid 会被用作实际的主键吗？

Only INTEGER PRIMARY KEYgets aliased with ROWID. Other kinds of primary keys don't, and there will be the implicit integer rowid unless WITHOUT ROWIDis specified. Reference.

只会INTEGER PRIMARY KEY被ROWID. 其他类型的主键没有，除非WITHOUT ROWID指定，否则会有隐式整数rowid 。参考。

回答by Segabond

In real world, using strings as primary key has a lot of benefits if we are talking about UUIDs. Being able to create entity "passport" exactly at the moment of its creation can massively simplify asynchronous code and/or distributed system (if we are talking about more complex mobile client / server architecture).

在现实世界中，如果我们谈论 UUID，使用字符串作为主键有很多好处。能够在实体创建的那一刻创建实体“护照”可以极大地简化异步代码和/或分布式系统（如果我们谈论的是更复杂的移动客户端/服务器架构）。

As to the performance, I did not find any measurable difference when running a benchmark to perform 10000 primary key lookups, as in reality, database indexes neither store nor compare strings when running indexed searches.

至于性能，我在运行基准测试以执行 10000 次主键查找时没有发现任何可衡量的差异，因为实际上，数据库索引在运行索引搜索时既不存储也不比较字符串。

回答by Bipin

Yes, if you use TEXT you get android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: TableName.ColumnName (code 1555)

是的，如果你使用 TEXT 你会得到 android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: TableName.ColumnName (code 1555)

SQLite has session to insert and return the row ID of the last row inserted, if this insert is successful. else will return -1.

如果此插入成功，SQLite 具有插入并返回最后插入行的行 ID 的会话。否则将返回-1。

return is mapped to _ID , this is the reason they force you interface BaseColumns for the table

return 映射到 _ID ，这就是他们强制您为表接口 BaseColumns 的原因

its strange that insert call has to return the rowid, instead of a boolean or so

奇怪的是插入调用必须返回rowid，而不是布尔值左右

I wish TEXT PRIMARY KEY capability was there in sqlite

我希望在 sqlite 中有 TEXT PRIMARY KEY 功能

回答by Sergio Cabral

A field of type PRIMARY KEY implies comparing values. Comparing a number is simpler than comparing a text.

PRIMARY KEY 类型的字段意味着比较值。比较数字比比较文本更简单。

The reason is that there is a specific assembly instruction for 64 bit numeric comparison. This will always be much faster than comparing text which in theory can be unlimited in size.

原因是64 位数字比较有特定的汇编指令。这总是比比较理论上可以无限大小的文本快得多。

Example comparing number:

比较数字示例：

CMP DX, 00  ; Compare the DX value with zero
JE  L7      ; If yes, then jump to label L7
.
.
L7: ...

Read more about CMPassembly instruction here: https://www.tutorialspoint.com/assembly_programming/assembly_conditions.htm

CMP在此处阅读有关汇编指令的更多信息：https: //www.tutorialspoint.com/assembly_programming/assembly_conditions.htm

Knowing this allows us to know that numbers will always be more performative(at least in the computing we have today).

知道这一点让我们知道数字总是更具表现力（至少在我们今天的计算中）。