Database Reference
In-Depth Information
In the above, the narrative is just in straightforward text, but the words that the character is
speaking is surrounded with quote marks, so we can tell the difference between the spoken
word and the story. MySQL needs to tell the difference between strings and the main syn-
tax of queries so that it does not think that there are errors in the query.
CHAR
The CHAR datatype is used to define a string of fixed length as follows:
columnname CHAR(length)
length is the width of the string in characters, which can be between 0 and 253.
Once you declare a column with a CHAR value of fixed length, all of the strings that are
stored in that column become that length. For example, suppose we used the following as a
column declaration:
columnname CHAR(10)
If we were to store the string “Hello, World” in a column created like the one above, the
system would only store “Hello, Wor”, the first 10 characters of the string. All of the remain-
ing characters are lost, and no error message is given. If you are storing a string less than 10
characters long, for instance, “Hello!”, then MySQL will pad out the string with spaces to
make it up to the 10 characters fixed length. So this will be stored as:
Hello!••••
where each • in the above represents a space. This can be an issue if you are searching
CHAR columns. For instance, if you are searching for the word “Hello!” in a CHAR(10) col-
umn, nothing would be found, instead you have to search for “Hello!••••” which is what the
datatype actually stores. Remember that each string has to be the length that the column
declaration requires. We will not deal with this now, but in Chapter 6 there is a way to search
for just the start of a string, which would allow you to find a string of less than the defined
length of the column.
VARCHAR
The CHAR datatype described above only allows fixed length strings. As you will have seen,
this can cause unexpected results in searching if you forget to pad out the search term to the
required length of the column. The VARCHAR datatype gets around this problem by allow-
ing a variable length of string up to 255 characters. You define a VARCHAR column as fol-
lows:
columnname VARCHAR(length)
length is the maximum width of the string in characters, which can be between 0 and 253.
Again, if we were to insert “Hello, World!” into this datatype when defined with a length
of 10, we would get “Hello, Wor” stored. However, if we inserted the string “Hello!” into it, it
Search WWH ::




Custom Search