常见的 MySQL 字段及其相应的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/354763/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Common MySQL fields and their appropriate data types
提问by Enrico
I am setting up a very small MySQL database that stores, first name, last name, email and phone number and am struggling to find the 'perfect' datatype for each field. I know there is no such thing as a perfect answer, but there must be some sort of common convention for commonly used fields such as these. For instance, I have determined that an unformatted US phone number is too big to be stored as an unsigned int, it must be at least a bigint.
我正在设置一个非常小的 MySQL 数据库，用于存储名字、姓氏、电子邮件和电话号码，并且正在努力为每个字段找到“完美”的数据类型。我知道没有完美的答案，但是对于诸如此类的常用领域，必须有某种通用约定。例如，我已经确定一个未格式化的美国电话号码太大而无法存储为 unsigned int，它必须至少是一个 bigint。
Because I am sure other people would probably find this useful, I dont want to restrict my question to just the fields I mentioned above.
因为我相信其他人可能会觉得这很有用，所以我不想将我的问题仅限于我上面提到的领域。
What datatypes are appropriate for common database fields? Fields like phone number, email and address?
哪些数据类型适用于常见的数据库字段？电话号码、电子邮件和地址等字段？
采纳答案by da5id
Someone's going to post a much better answer than this, but just wanted to make the point that personally I would never store a phone number in any kind of integer field, mainly because:
有人会发布比这更好的答案，但只是想说明我个人永远不会将电话号码存储在任何类型的整数字段中，主要是因为：
- You don't need to do any kind of arithmetic with it, and
- Sooner or later someone's going to try to (do something like) put brackets around their area code.
- 你不需要用它做任何算术运算，而且
- 迟早有人会尝试（做类似的事情）在他们的区号周围加上括号。
In general though, I seem to almost exclusively use:
但总的来说，我似乎几乎只使用：
- INT(11) for anything that is either an ID or references another ID
- DATETIME for time stamps
- VARCHAR(255) for anything guaranteed to be under 255 characters (page titles, names, etc)
- TEXT for pretty much everything else.
- INT(11) 表示 ID 或引用另一个 ID 的任何内容
- DATETIME 时间戳
- VARCHAR(255) 用于保证少于 255 个字符的任何内容（页面标题、名称等）
- 几乎所有其他内容的文本。
Of course there are exceptions, but I find that covers most eventualities.
当然也有例外，但我发现这涵盖了大多数情况。
回答by yentsun
Here are some common datatypes I use (I am not much of a pro though):
以下是我使用的一些常见数据类型（虽然我不是很专业）：
| Column | Data type | Note
| ---------------- | ------------- | -------------------------------------
| id | INTEGER | AUTO_INCREMENT, UNSIGNED |
| uuid | CHAR(36) | or CHAR(16) binary |
| title | VARCHAR(255) | |
| full name | VARCHAR(70) | |
| gender | TINYINT | UNSIGNED |
| description | TINYTEXT | often may not be enough, use TEXT
instead
| post body | TEXT | |
| email | VARCHAR(255) | |
| url | VARCHAR(2083) | MySQL version < 5.0.3 - use TEXT |
| salt | CHAR(x) | randomly generated string, usually of
fixed length (x)
| digest (md5) | CHAR(32) | |
| phone number | VARCHAR(20) | |
| US zip code | CHAR(5) | Use CHAR(10) if you store extended
codes
| US/Canada p.code | CHAR(6) | |
| file path | VARCHAR(255) | |
| 5-star rating | DECIMAL(3,2) | UNSIGNED |
| price | DECIMAL(10,2) | UNSIGNED |
| date (creation) | DATE/DATETIME | usually displayed as initial date of
a post |
| date (tracking) | TIMESTAMP | can be used for tracking changes in a
post |
| tags, categories | TINYTEXT | comma separated values * |
| status | TINYINT(1) | 1 – published, 0 – unpublished, … You
can also use ENUM for human-readable
values
| json data | JSON | or LONGTEXT
回答by staticsan
In my experience, first name/last name fields should be at least 48 characters -- there are names from some countries such as Malaysia or India that are very long in their full form.
根据我的经验，名字/姓氏字段应至少包含 48 个字符——有些国家的姓名（如马来西亚或印度）的完整形式很长。
Phone numbers and postcodes you should alwaystreat as text, not numbers. The normal reason given is that there are postcodes that begin with 0, and in some countries, phone numbers can also begin with 0. But the real reason is that they aren't numbers-- they're identifiersthat happen to be made up of numerical digits (and that's ignoring countries like Canada that have letters in their postcodes). So store them in a text field.
您应该始终将电话号码和邮政编码视为文本，而不是数字。给出的正常原因是邮政编码以 0 开头，在某些国家/地区，电话号码也可以以 0 开头。但真正的原因是它们不是数字——它们是碰巧组成的标识符数字（这忽略了像加拿大这样邮政编码中有字母的国家）。因此，将它们存储在文本字段中。
In MySQL you can use VARCHAR fields for this type of information. Whilst it sounds lazy, it means you don't have to be too concerned about the right minimum size.
在 MySQL 中，您可以将 VARCHAR 字段用于此类信息。虽然听起来很懒惰，但这意味着您不必太担心正确的最小尺寸。
回答by nickf
Since you're going to be dealing with data of a variable length (names, email addresses), then you'd be wanting to use VARCHAR. The amount of space taken up by a VARCHAR field is [field length]+ 1 bytes, up to max length 255, so I wouldn't worry too much about trying to find a perfect size. Take a look at what you'd imagine might be the longest length might be, then double it and set that as your VARCHAR limit. That said...:
由于您要处理可变长度的数据（姓名、电子邮件地址），因此您会想要使用 VARCHAR。VARCHAR 字段占用的空间量为[field length]+ 1 个字节，最大长度为 255，因此我不会太担心试图找到完美的大小。看看您想象的最长长度可能是多少，然后将其加倍并将其设置为您的 VARCHAR 限制。那是说...：
I generally set email fields to be VARCHAR(100) - i haven't come up with a problem from that yet. Names I set to VARCHAR(50).
我通常将电子邮件字段设置为 VARCHAR(100) - 我还没有从中提出问题。我设置为 VARCHAR(50) 的名称。
As the others have said, phone numbers and zip/postal codes are not actually numeric values, they're strings containing the digits 0-9 (and sometimes more!), and therefore you should treat them as a string. VARCHAR(20) should be well sufficient.
正如其他人所说，电话号码和邮政编码实际上并不是数字值，它们是包含数字 0-9（有时甚至更多！）的字符串，因此您应该将它们视为字符串。VARCHAR(20) 应该足够了。
Note that if you were to store phone numbers as integers, many systems will assume that a number starting with 0 is an octal (base 8) number! Therefore, the perfectly valid phone number "0731602412" would get put into your database as the decimal number "124192010"!!
请注意，如果您要将电话号码存储为整数，许多系统会假定以 0 开头的数字是八进制（以 8 为基数）数字！因此，完全有效的电话号码“0731602412”将作为十进制数“124192010”放入您的数据库中！！
回答by Thomas
I am doing about the same thing, and here's what I did.
我正在做同样的事情，这就是我所做的。
I used separate tables for name, address, email, and numbers, each with a NameID column that is a foreign key on everything except the Name table, on which it is the primary clustered key. I used MainName and FirstName instead of LastName and FirstName to allow for business entries as well as personal entries, but you may not have a need for that.
我为姓名、地址、电子邮件和号码使用了单独的表，每个表都有一个 NameID 列，该列是除 Name 表之外的所有内容的外键，在 Name 表上它是主聚集键。我使用 MainName 和 FirstName 而不是 LastName 和 FirstName 来允许企业条目和个人条目，但您可能不需要。
The NameID column gets to be a smallint in all the tables because I'm fairly certain I won't make more than 32000 entries. Almost everything else is varchar(n) ranging from 20 to 200, depending on what you wanna store (Birthdays, comments, emails, really long names). That is really dependent on what kind of stuff you're storing.
NameID 列在所有表中都是 smallint，因为我相当确定我不会创建超过 32000 个条目。几乎所有其他内容都是 varchar(n)，范围从 20 到 200，具体取决于您想要存储的内容（生日、评论、电子邮件、非常长的名字）。这实际上取决于您存储的内容类型。
The Numbers table is where I deviate from that. I set it up to have five columns labeled NameID, Phone#, CountryCode, Extension, and PhoneType. I already discussed NameID. Phone# is varchar(12) with a check constraint looking something like this: CHECK (Phone# like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'). This ensures that only what I want makes it into the database and the data stays very consistent. The extension and country codes I called nullable smallints, but those could be varchar if you wanted to. PhoneType is varchar(20) and is not nullable.
数字表是我偏离的地方。我将它设置为有五列，分别标记为 NameID、Phone#、CountryCode、Extension 和 PhoneType。我已经讨论过 NameID。Phone# 是 varchar(12)，带有如下所示的检查约束： CHECK (Phone# like '[0-9][0-9][0-9]-[0-9][0-9][0 -9]-[0-9][0-9][0-9][0-9]')。这确保只有我想要的东西才能进入数据库并且数据保持非常一致。我将扩展名和国家/地区代码称为可空小整数，但如果您愿意，它们可以是 varchar。PhoneType 是 varchar(20) 并且不可为空。
Hope this helps!
希望这可以帮助！
