| home / programming / mysql_data / 1 |    [previous][next] |
|
|
|
MySQL knows about several general categories in which data values can be represented.
These include numbers, string values, temporal values such as dates and times, spatial values,
and the NULL value.
Numbers are values such as 48 or 193.62. MySQL understands numbers specified as
integers (which have no fractional part) and floating-point or fixed-point values (which
may have a fractional part). Integers can be specified in decimal or hexadecimal format.
An integer consists of a sequence of digits with no decimal point. In numeric contexts,
an integer can be specified as a hexadecimal constant and is treated as a 64-bit
integer. For example, 0x10 is 16 decimal. Hexadecimal values are treated as strings by
default, so their syntax is given in the next section, String Values.
A floating-point or fixed-point number consists of a sequence of digits, a decimal point, and another sequence of digits.The sequence of digits before or after the decimal point may be empty, but not both.
MySQL understands scientific notation.This is indicated by immediately following an
integer or floating-point number with e or E, a sign character (+ or -), and an integer
exponent. 1.34E+12 and 43.27e-1 are legal numbers in scientific notation.The
number 1.34E12 is also legal even though it is missing an optional sign character before
the exponent.
Hexadecimal numbers cannot be used in scientific notation; the e that begins the exponent part is also a legal hex digit and thus would be ambiguous. Any number can be preceded by a plus or minus sign character (+ or -), to indicate a positive or negative value.
As of MySQL 5.0.3, bit-field values can be written as b'val', where val consists of
one or more binary digits (0 or 1). For example, b'1001' is 9 decimal.This notation
coincides with the introduction of the BIT data type, but bit-field values can be used
more generally in other contexts.
Strings are values such as 'Madison, Wisconsin', 'patient shows improvement', or
even '12345' (which looks like a number, but isnt). Usually, you can use either single or
double quotes to surround a string value, but there are two reasons to stick with single
quotes:
|
For the examples that use the double quote as a string quoting character in the discussion
that follows, assume that ANSI_QUOTES mode is not enabled.
MySQL recognizes several escape sequences within strings that indicate special characters,
as shown in Table 3.1. Each sequence begins with a backslash character (\) to
signify a temporary escape from the usual rules for character interpretation. Note that a
NUL byte is not the same as the SQL NULL value; NUL is a zero-valued byte, whereas
NULL in SQL signifies the absence of a value.
The escape sequences shown in the table are case sensitive, and any character not listed
in the table is interpreted as itself if preceded by a backslash. For example, \t is a tab, but
\T is an ordinary T character.
The table shows how to escape single or double quotes using backslash sequences, but you actually have several options for including quote characters within string values:
'I can''t' "He said, ""I told you so.""" Quote the string with the other quote character. In this case, you do not double the quote characters within the string: "I can't" 'He said, "I told you so."' Escape the quote character with a backslash; this works regardless of the quote characters used to quote the string: 'I can\'t' "I can\'t" "He said, \"I told you so.\"" 'He said, \"I told you so.\"' |
To turn off the special meaning of backslash and treat it as an ordinary character, enable
the NO_BACKSLASH_ESCAPES SQL mode, which is available as of MySQL 5.0.2.
As an alternative to using quotes for writing string values, you can use two forms of
hexadecimal notation.The first consists of 0x followed by one or more hexadecimal
digits (0 through 9 and a through f). For example, 0x0a is 10 decimal, and 0xffff is
65535 decimal.The non-decimal hex digits (a through f) can be specified in uppercase
or lowercase, but the leading 0x cannot be given as 0X.That is, 0x0a and 0x0A are
legal hexadecimal values, but 0X0a and 0X0A are not. In string contexts, pairs of hexadecimal
digits are interpreted as 8-bit numeric byte values in the range from 0 to 255, and
the result is used as a string. In numeric contexts, a hexadecimal constant is treated as a
number.The following statement illustrates the interpretation of a hex constant in each
type of context:
If a hexadecimal value written using 0x notation has an odd number of hex digits,
MySQL treats it as though the value has a leading zero. For example, 0xa is treated as
0x0a.
String values may also be specified using the standard SQL notation X'val', where
val consists of pairs of hexadecimal digits. As with 0x notation, such values are interpreted
as strings, but may be used as numbers in a numeric context:
Unlike 0x notation, the leading X is not case sensitive:
| home / programming / mysql_data / 1 | [previous][next] |
Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia
Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers
Created: March 27, 2003
Revised: April 24, 2006
URL: http://webreference.com/programming/mysql_data/1
