I decided to make a MySQL Data Type Cheat Sheet to make it easier for me, and my students, to find appropriate data types for our MySQL databases.

Step One – MySQL Text Columns

FIRST – Look at your text (string) data, Ask yourself if the data column will be indexed? That means will us use it to search with? You may be searching for things like “user_name”, “password”, “id”, cell_phone_number, and date_of_birth. If it is text and you plan to search it, use a VARCHAR data type. If it is text and you do not plan to search (index) it, use a TEXT data type.

Step Two – Find a MySQL Column Data Type

Second – Now just look on this simple table and find a field (column name) that is pretty close to the data you are planning to store in your database. Chances are that one of these 22 will be all you need. Or you will see how easy it is to modify it!


Column Data type Comment
date (creation) BIGINT(20) Best for manipulation in PHP (unix timestamp) where NOT Indexed in MySQL (I use this the most!)
date (creation) DATE / DATETIME Best for MySQL Indexing where time is needed. Usually displayed as initial date of a post.
date (tracking) TIMESTAMP May be used for tracking changes in a post. I prefer doing it in PHP with BIGINT.
date_of_birth DATE Best for date of birth/death where no time is needed. Also date_ joined, or date_started/stopped.
description TINYTEXT NO INDEX! May not be enough, use TEXT instead.
email VARCHAR(255)
file_name VARCHAR(255) Path should be encoded in a php script, not taking up room in a database.
five_star_rating DECIMAL(3,2) UNSIGNED Five-Star Rating or Karma
full_name VARCHAR(70) Or given-name, middle_name, and family_name.
gender TINYINT(1) / CHAR(1) CHAR: M=Male, F=Female. TINYINT: UNSIGNED 1=Transgender Female Pre-OP
password VARCHAR(255) / CHAR(32) Best use bcrypt! Digest: md5=CHAR(32) adjust fixed length for sha1, sha256, bcrypt etc.
phone_number BIGINT(20) All Numbers, start with country code only. Only 8 bytes!
phone_number VARCHAR(20)
post_body TEXT NO INDEX!
price DECIMAL(10,2) UNSIGNED May need more in the Philippines or Japan.
salt_or_code CHAR(x) Randomly generated string, usually of fixed length (x)
status TINYINT(1) / CHAR(1) 1 or P = published, 2 or U = unpublished.
tags, categories TINYTEXT NO INDEX! Comma separated values *
title VARCHAR(255)
url TEXT NO INDEX! MySQL version > 5.0.3: can use VARCHAR(2083) w/INDEX
zip_code CHAR(4) (5), (6), (10) Philippines CHAR(4), USA CHAR(5), Canada: CHAR(6) All others (USA extended codes): CHAR(10)

Step Three – MySQL Field Names

The MySQL Field (Column) Name is important. It MUST be descriptive of what is in that field. For example, I have seen fields named “date”, “text”, and “sex”. In one class I had, half my students built a database structure, and the other half of the students would make statements to fill in the data, based solely on the field name only. One student had a field that was “sex”. Confused as to what information the student was asking for, the other student populated the field with a simple “Yes!” Wonder what she was thinking? The student wanted an answer like “Male” or “Female”. A better field name in this case would have been “gender”.

For field names / column names, use one to three words, in lowercase, separated with underscores.

“date_of_birth” and “membership_date” are much clearer field names, than the student who just wrote “date” so he would have less to type, and more time to spend dating! The other student simply answered “No!” because she was too busy studying, and she graduated with honors! (Note: He did end up with more time for dating. He failed a number of courses and was there for another year of dating!)

“creation_date” and “last_update” would both be preferred to “date7″ and date8” in being understood.

“Text” goes without saying as one of the worst field names in history. During my class discussion about the above field name “sex”, some of the students were taking a reproductive health class at the same time, and were discussing being sexually active. Therefore, “sexually_active” and “sexual_preference” became suggestions for how to improve the “sex” field name.

The point here is that databases stay around for a long, long time. Even outliving people. I have decades old databases! So choose your field and column names very carefully.

You have about 32 characters for each one, use 2-3 whole words, in lowercase, and tie the words together with underscores. You will be glad you did.

P.S. I am currently working on a database that has the fields, “bio”, “time”, “confirmed”, and “generated_string”. These are all fails because the programmer failed to convey to me the actual meaning in these field names.