MySQL Data Methods | 2

MySQL Data Methods

Categories of Data Values

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.

Numeric Values

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.

String Values

Strings are values such as 'Madison, Wisconsin', 'patient shows improvement', or even '12345' (which looks like a number, but isn't). Usually, you can use either single or double quotes to surround a string value, but there are two reasons to stick with single quotes:

  • The SQL standard specifies single quotes, so statements that use single-quoted strings are more portable to other database engines.

  • If the ANSI_QUOTES SQL mode is enabled, it treats the double quote as an identifier quoting character, not as a string quoting character.This means that a doublequoted value must refer to something like a database or table.

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:

  • Double the quote character if the string itself is quoted using the same character:

'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:

    Created: March 27, 2003
    Revised: April 24, 2006

    URL: http://webreference.com/programming/mysql_data/1