Database Reference
In-Depth Information
Fixed-length strings are datatypes
that are defined to accept a fixed number of
characters, and that number is specified when the table is created. For example,
you might allow 30 characters in a first-name column or 11 characters in a
Social-Security-number column (the exact number needed allowing for the
two dashes). Fixed-length columns do not allow more than the specified num-
ber of characters. They also allocate storage space for as many characters as
specified. So, if the string
Ben
is stored in a 30-character first-name field, a full
30 bytes are stored.
CHAR
is an example of a fixed-length string type.
Variable-length strings store text of variable length. Some variable-length
datatypes have a defined maximum size. Others are entirely variable. Either
way, only the data specified is saved (and no extra data is stored).
TEXT
is an
example of a variable-length string type.
If variable-length datatypes are so flexible, why would you ever want to use
fixed-length datatypes? The answer is performance. MariaDB can sort and
manipulate fixed-length columns far more quickly than it can sort variable-
length columns. In addition, MariaDB does not allow you to index variable-
length columns (or the variable portion of a column). This also dramatically
affects performance.
Table D.1
String Datatypes
Datatype
Description
CHAR
Fixed-length string from 1 to 255 chars long. Its size
must be specified at create time, or MariaDB assumes
CHAR(1)
.
ENUM
Accepts one of a predefined set of up to 64K strings.
LONGTEXT
Same as
TEXT
, but with a maximum size of 4GB.
MEDIUMTEXT
Same as
TEXT
, but with a maximum size of 16K.
SET
Accepts zero or more of a predefined set of up to 64 strings.
TEXT
Variable-length text with a maximum size of 64K.
TINYTEXT
Same as
TEXT
, but with a maximum size of 255 bytes.
VARCHAR
Same as
CHAR
, but stores just the text. The size is a maxi-
mum, not a minimum.
Tip
Using Quotes Regardless of the form of string datatype being used, string values must
always be surrounded by quotes (single quotes are often preferred).
