Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

MySQL Commands – Part 3

Saturday, July 5th, 2014 by Servage

php-explorer-tab-search

With this article we continue our series about essential MySQL Commands.

Data Types (cont.)

The year field, however, has more predictable values, so instead of VARCHAR we use the more efficient CHAR(4) data type. The parameter of 4 allows for four bytes of data, supporting all years from –999 to 9999. You could, of course, just store two-digit values for the year, but if your data is going to still be needed in the following century, or may otherwise wrap around, it will have to be sanitized first—much like the “millennium bug” that would have caused dates beginning on January 1, 2000, to be treated as 1900 on many of the world’s biggest computer installations.

Both CHAR and VARCHAR accept text strings and impose a limit on the size of the field. The difference is that every string in a CHAR field has the specified size. If you put in a smaller string, it is padded with spaces. A VARCHAR field does not pad the text; it lets the size of the field vary to fit the text that is inserted. But VARCHAR requires a small amount of overhead to keep track of the size of each value, so CHAR is slightly more efficient if the sizes are similar in all records (whereas VARCHAR is more efficient if the sizes can vary a lot and get large). In addition, the overhead causes access to VARCHAR data to be slightly slower than to CHAR data.

The CHAR data type

All these CHAR data types offer a parameter that sets the maximum (or exact) length of the string allowed in the field. As the table shows, each type also has a built-in maximum. Types of VARCHAR between 0 and 255 bytes in length incur a storage overhead of 1 byte, or 2 bytes overhead if 256 bytes or more.

The BINARY data type

The BINARY data type is used for storing strings of full bytes that do not have an associated character set. For example, you might use the BINARY data type to store a GIF image.

The TEXT and VARCHAR data types

The differences between TEXT and VARCHAR are small:

• Prior to version 5.0.3, MySQL removed leading and trailing spaces from VARCHAR fields, and they could only be up to 256 bytes in length.

• TEXT fields cannot have default values.

• MySQL indexes only the first “n” characters of a TEXT column (you specify “n” when you create the index).

What this means is that VARCHAR is the better and faster data type to use if you need to search the entire contents of a field. If you will never search more than a certain number of leading characters in a field, you should probably use a TEXT data type.

The BLOB data type

The term BLOB stands for Binary Large OBject, and therefore, as you would think, the BLOB data type is most useful for binary data in excess of 65,536 bytes in size. The main other difference between the BLOB and BINARY data types is that BLOBs cannot have default values.

Sources for further reading

MySQL Commands – Part 3, 5.0 out of 5 based on 1 rating
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.