使用文本作为SQLite表的主键不好吗?-UsingtextasaprimarykeyinSQLitetablebad?

-7  

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 key in table. For me (and I honestly hope also for others) very "ugly" practise with 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?
• 3个符号组合够吗?
• Or should I use 5 symbols?
• 或者我应该用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².

在这里，每一行必须具有相同的格式(当然是可读性问题)，并且也是唯一的。哦!下面是下一个“piggy work”—您需要创建一些“唯一的字符串生成器”来生成unique1字符串标识符2。

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!

这是一个更复杂的主题，但是我想说，好吧，对于非常小的表来说，可以使用string作为主键(如果它会产生一个sence)，但是如果您看到它的缺点，那么使用number作为主键肯定会更好!

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?).

我不建议您使用string作为主键。它有更多的缺点作为优点(它真的有一些优点吗?)

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).}

当然，你也可以说你可以根据项目的名称来创建标识符，但是它还是意大利面条式的代码(项目可以有相同的名称)。

15  

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 KEY is all right.

从正确性的角度来看，文本主键是可以的。

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

从性能的角度来看，更喜欢整数键。但是与任何性能问题一样，您自己度量它，看看您的数据和用例是否有显著的差异。

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

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

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

只有整数主键使用ROWID进行别名。其他类型的主键没有，并且会有隐式整数rowid，除非没有指定rowid。参考。

6  

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，使用字符串作为主键有很多好处。能够在创建实体时创建“passport”，可以极大地简化异步代码和/或分布式系统(如果我们讨论的是更复杂的移动客户端/服务器架构)。

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个主键查找时没有发现任何可测量的差异，实际上，数据库索引在运行索引搜索时既不存储也不比较字符串。