常见的MySQL字段及其相应的数据类型 [英] Common MySQL fields and their appropriate data types

问题描述

我设置了一个非常小的MySQL数据库，存储，名字，姓氏，电子邮件和电话号码，并努力找到每个字段的完美数据类型。我知道没有这样的东西作为一个完美的答案，但必须有一些常见的惯例，如这些常用的领域。例如，我确定一个未格式化的美国电话号码太大，无法存储为无符号整数，它必须至少是bigint。

因为我相信其他人可能会发现这很有用，我不想限制我的问题只是我上面提到的字段。

什么数据类型适用于常见的数据库字段？像电话号码，电子邮件和地址等字段？

解决方案

有人会发布比这更好的答案，我个人不会在任何类型的整数字段中存储电话号码，这主要是因为：

1. 你不需要做

一般来说，我似乎几乎只使用：

• 任何是ID或引用另一个ID


• DATETIME用于时间戳


• VARCHAR

当然，这里有一些标题，名称等）
<是例外，但我发现涵盖大多数可能性。

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.

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?

解决方案

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:

1. You don't need to do any kind of arithmetic with it, and
2. 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.

Of course there are exceptions, but I find that covers most eventualities.

这篇关于常见的MySQL字段及其相应的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！