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.
VARCHAR is actually part of the ISO SQL:2003 standard; The TEXT data types, including TINYTEXT, are non-standard.
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.[Read more]
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
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
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
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
You can download:
Or just watch the video: