Having reviewed different table structures designed by different people, I have come to the conclusion that binary and non-binary string data types are used without consideration of the consequences of choosing either one. The confusion stems from the fact that both non-binary and binary string data appear to store characters because they can be saved as quoted string.

On first glance, it looks like TEXT and VARCHAR can store the same information. However, there are fundamental differences between the way TEXT fields and VARCHAR fields work, which are important to take into consideration.

Standard
VARCHAR is actually part of the ISO SQL:2003 standard; The TEXT data types, including TINYTEXT, are non-standard.

Storage
TEXT data types are stored as separate objects from the tables and result sets that contain them. This storage is transparent — there is no difference in how a query involving a TEXT field is written versus one involving a VARCHAR field. Since TEXT is not stored as part of a row, retrieval of TEXT fields requires extra [edited 1/22] memory overhead.

Maximum VARCHAR length
The maximum row length of a VARCHAR is restricted by the maximum row length of a table. This is 65,535 bytes …

This is the 137th edition of Log Buffer, the weekly review of database blogs. Dave Edwards is enjoying a week off, and so as part of my plot to take over the world, I am writing this week’s Log Buffer.

First, the fun stuff: Josh Berkus tells us that the American English Translation of the Manga Guide to Databases is available in Japanese Fairies and Third Normal Form.

Then, the basics:
Giri Mandalika points to an article on Using MySQL with Java Technology. This is a basic article on how to connect, and …

Or, “Missing information in the MySQL Manual”.

Just earlier today, I was using POW(), which I’ve grown quite fond of, simply because it makes life easier. I prefer using it like SELECT 512*POW(1024,2) to find out the number of bytes to put in a variable, for example.

First, let’s take a look at the POW function:

Name: 'POW'
Description:
Syntax:
POW(X,Y)

Returns the value of X raised to the power of Y.

Okay, so it gives us a value; but what about the data type? Let’s take 512*POW(1024,2) as an example.

5067 (blogs) > SELECT 512*POW(1024,2) AS example;
+-----------+
| example   |
+-----------+
| 536870912 | 
+-----------+
1 row in set (0.00 sec)

What is that? Well, it sure does look like an INT at this point, …

Here is the video of “Database Basics”, which I presented at the February 2008 Boston MySQL User Group meeting. The presentation goes over the basics of relations, data, the Entity-Relationship Model, how to choose data types, and how to do basic CREATE statements.

You can download:

the video (Large, 500 MB, or Small, 100 MB)
and
the slides (PDF, 171 Kb).

Or just watch the video: