mysql - 如何为列选择优化数据类型 [innodb specific]？

Question

我正在学习数据库数据类型的使用。
例如:

哪个更适合电子邮件？ varchar[100]、char[100] 或 tinyint(开玩笑)

哪个用户名更好？我应该使用 int、bigint 还是 varchar？
解释。我的一些 friend 说，如果我们使用 int、bigint 或其他数字数据类型会更好(facebook 这样做)。比如 u=123400023 指的是用户 123400023，而不是 user=thenameoftheuser。由于数字需要更少的时间来获取。

哪个更适合电话号码？帖子(例如在博客或公告中)？或者日期(我使用日期时间)？也许有些人做了研究，想分享。

产品价格(我使用十进制(11,2)，不知道你们)？

或者您想到的任何其他内容，例如“我对 blablabla 使用串行数据类型”。

为什么我要特别提到innodb？

Unless you are using the InnoDB tabletypes (see Chapter 11, "AdvancedMySQL," for more information), CHARcolumns are faster to access thanVARCHAR.

Inno db 有一些我不知道的差异。
我从 here 读到的.

Answer 1

简要总结:

(只是我的意见)

用于电子邮件地址 - VARCHAR(255)

对于用户名 - VARCHAR(100)或 VARCHAR(255)

对于 id_username - 使用 INT (除非您计划在您的系统中拥有超过 20 亿用户)

电话号码 - INT或 VARCHAR或者 CHAR (取决于您是否要存储格式)

帖子- TEXT

日期 - DATE或 DATETIME (绝对包括帖子或电子邮件之类的时间)

钱- DECIMAL(11,2)

杂项 - 见下文

至于使用 InnoDB，因为 VARCHAR应该更快，我不会担心，或者一般的速度。使用 InnoDB 是因为您需要执行事务和/或您想使用外键约束 (FK) 来确保数据完整性。此外，InnoDB 使用行级锁定，而 MyISAM 仅使用表级锁定。因此，InnoDB 可以比 MyISAM 更好地处理更高级别的并发。使用 MyISAM 来使用全文索引并减少开销。

比引擎类型更重要的是速度:将索引放在您需要快速搜索的列上。始终将索引放在您的 ID/PK 列上，例如我提到的 id_username。

更多详情:

这里有一堆关于 MySQL 数据类型和数据库设计的问题(警告，比你要求的要多):

What DataType should I pick?

Table design question

Enum datatype versus table of data in MySQL?

mysql datatype for telephne number and address

Best mysql datatype for grams, milligrams, micrograms and kilojoule

MySQL 5-star rating datatype?

还有一些关于何时使用 InnoDB 引擎的问题:

MyISAM versus InnoDB

When should you choose to use InnoDB in MySQL?

我只是用 tinyint几乎所有事情(严重)。

编辑 - 如何存储“帖子:”

下面是一些包含更多详细信息的链接，但这里是简短版本。为了存储“帖子”，您需要为长文本字符串留出空间。 CHAR最大长度是 255，所以这不是一个选项，当然 CHAR与 VARCHAR 相比，会浪费未使用的字符，这是可变长度 CHAR .

在 MySQL 5.0.3 之前， VARCHAR最大长度是 255，所以你会剩下 TEXT .但是，在较新版本的 MySQL 中，您可以使用 VARCHAR或 TEXT .选择归结为偏好，但存在一些差异。 VARCHAR和 TEXT最大长度现在都是 65,535，但您可以在 VARCHAR 上设置您自己的最大长度.假设您认为您的帖子最多只需要 2000，您可以设置 VARCHAR(2000) .如果你每次都遇到极限，你可以 ALTER稍后您将 table 撞到 VARCHAR(3000) .另一方面， TEXT实际上将其数据存储在 BLOB 中(1).我听说 VARCHAR 之间可能存在性能差异和 TEXT ，但我还没有看到任何证据，因此您可能想进一步研究，但您将来可以随时更改该小细节。

更重要的是，使用全文索引而不是 LIKE 搜索此“post”列。会快得多(2)。然而， 您必须使用 MyISAM 引擎才能使用全文索引，因为 InnoDB 不支持它 .在 MySQL 数据库中，您可以为每个表使用异构引擎组合，因此您只需要让“posts”表使用 MyISAM。但是，如果您绝对需要“posts”来使用 InnoDB(用于事务)，那么设置一个触发器来更新“posts”表的 MyISAM 副本，并使用 MyISAM 副本进行所有全文搜索。

有关一些有用的报价，请参阅底部。

MySQL Data Type Chart (outdated)

MySQL Datatypes (outdated)

Chapter 10. Data Types (better details)

The BLOB and TEXT Types (1)

11.9. Full-Text Search Functions (2)

10.4.1. The CHAR and VARCHAR Types (3)

(3) "Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

Before MySQL 5.0.3, if you need a data type for which trailing spaces are not removed, consider using a BLOB or TEXT type.

When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values."

最后，这是一篇关于 VARCHAR 与 TEXT 的优缺点的好文章。它还涉及性能问题:

VARCHAR(n) Considered Harmful