MySQL-使用字符串作为主键 [英] MySQL - using String as Primary Key

问题描述

我已经在Stack Overflow上看到过类似的帖子，但并不十分满意.

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

假设我提供了Web服务. http://foo.com/SERVICEID

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

SERVICEID是用于引用服务的唯一字符串ID(以64为基数，小写/大写+数字)，类似于URL缩短程序服务如何为URL生成ID.

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.

我了解比较字符串和整数存在固有的性能问题.

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

但是我很好奇如何最大程度地优化String类型的主键.

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

我正在使用MySQL(目前使用MyISAM引擎，尽管我承认并不了解所有引擎差异).

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

谢谢.

更新出于我的目的，该字符串实际上只是一个以base62编码的整数，因此主键是一个整数，并且由于您不太可能超过bigint的大小，因此它不会使用其他任何东西(对于我的特定用例)而言太有道理

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)

推荐答案

使用CHAR或VARCHAR作为主键没有任何问题.

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

在很多情况下，它肯定会比INT占用更多的空间，但是在很多情况下，这是最合乎逻辑的选择，甚至可以通过避免需求来减少所需的列数，从而提高效率有一个单独的ID字段.

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.

例如，国家代码或州缩写已经具有标准化的字符代码，这将是使用基于字符的主键而不是为每个字符组成任意整数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.

这篇关于MySQL-使用字符串作为主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！