Database Reference
In-Depth Information
would store “Hello!” with no extra spaces. Searching the column for “Hello!” would find the
string with no further manipulation needed.
Fixed or Variable Length?
Although variable length datatypes as mentioned above make it easier for searching, using
fixed length types have the advantage that they are faster to search. When you are design-
ing your tables, if you want the searches to be faster you should consider using fixed length
types.
Variable length types also can produce the need to perform maintenance on the table
periodically. If you make lots of changes to a table with variable length rows, over time the
table will become fragmented due to entries and space becoming de-allocated and re-
allocated. MySQL provides some commands to remedy this which you can read about in
Chapter 13 on Optimizing MySQL.
You should consider these issues when choosing the datatypes for your columns.
Storing Text
CHAR and VARCHAR are great for storing strings like people's names and addresses, but
they have the limit that the string can be a maximum length of 255 characters. If we are
storing things like the comments of a user to a website, or a topic review, or other large
blocks of text then this limit is prohibitive. The TEXT datatype gets around this limit by
defining a string that has a much bigger limit. A TEXT column can contain strings that have
a maximum of 65 535 characters. This should cover most things that a webpage user should
type into a webpage! If we take the average word to be 5 characters long, that is still over
13 000 words. You define a TEXT column as follows:
columnname TEXT
It is easy to be tempted to define all columns that are going to contain strings as TEXT.
Although this will stop you from running out of space when storing a string, it does place
much more strain on the MySQL engine when sorting through lots of TEXT columns. It is
much quicker to search through columns of CHAR and VARCHAR for small strings,
because of the way that MySQL stores the TEXT datatype. If you want a fast database server,
getting the datatypes correct is important.
MySQL also supports three other types of TEXT datatype: TINYTEXT, MEDIUMTEXT
and LONGTEXT. Table 3.4 shows the attributes of all of the TEXT datatypes.
Table 3.4
Text datatypes.
Declaration
Maximum length of string in characters
columnname TINYTEXT
255
columnname TEXT
65 535
columnname MEDIUMTEXT
16 777 215
columnname LONGTEXT
4 294 967 295
Search WWH ::
Custom Search