Common MySQL fields and their appropriate data types
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.
Of course there are exceptions, but I find that covers most eventualities.
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