MySQL 字符串作为key_MySQL -使用字符串作為主鍵

I saw a similar post on Stack Overflow already, but wasn't quite satisfied.

我在Stack Overflow上看到過類似的帖子，但並不十分滿意。

Let's say I offer a Web service. http://foo.com/SERVICEID

假設我提供一個Web服務。http://foo.com/SERVICEID

SERVICEID is a unique String ID used to reference the service (base 64, lower/uppercase + numbers), similar to how URL shortener services generate ID's for a URL.

SERVICEID是一個唯一的字符串ID，用於引用服務(base 64，小寫+數字)，類似於URL shortener服務如何為URL生成ID。

I understand that there are inherent performance issues with comparing strings versus integers.

我理解在比較字符串和整數之間存在固有的性能問題。

But I am curious of how to maximally optimize a primary key of type String.

但是我很好奇如何最大限度地優化字符串類型的主鍵。

I am using MySQL, (currently using MyISAM engine, though I admittedly don't understand all the engine differences).

我正在使用MySQL(目前使用MyISAM引擎，雖然我確實不理解所有的引擎差異)。

Thanks.

謝謝。

update for my purpose the string was actually just a base62 encoded integer, so the primary key was an integer, and since you're not likely to ever exceed bigint's size it just doesn't make too much sense to use anything else (for my particular use case)

出於我的目的進行更新字符串實際上只是一個base62編碼的整數，所以主鍵是一個整數，由於您不太可能超過bigint的大小，因此使用其他任何東西(對於我的特定用例)都沒有多大意義

1 个解决方案

#1

41

There's nothing wrong with using a CHAR or VARCHAR as a primary key.

使用CHAR或VARCHAR作為主鍵沒有任何問題。

Sure it'll take up a little more space than an INT in many cases, but there are many cases where it is the most logical choice and may even reduce the number of columns you need, improving efficiency, by avoiding the need to have a separate ID field.

當然，在許多情況下，它會比INT占用更多的空間，但是在很多情況下，它是最符合邏輯的選擇，甚至可能減少所需的列的數量，通過避免使用單獨的ID字段來提高效率。

For instance, country codes or state abbreviations already have standardised character codes and this would be a good reason to use a character based primary key rather than make up an arbitrary integer ID for each in addition.

例如，國家代碼或國家縮寫已經有了標准化的字符代碼，這將是一個很好的理由來使用基於字符的主鍵，而不是為每個字符加上任意的整數ID。