Chapter 2. Working with Data in MySQL

Virtually everything you do in MySQL involves data in some way or another because the purpose of a database management system is, by definition, to manage data. Even a simple SELECT.1 statement involves expression evaluation to produce an integer data value.

Every data value in MySQL has a type. For example, 37.4 is a number, and "abc" is a string. Sometimes data types are explicit, as when you issue a CREATE TABLE statement that specifies the type for each column you declare as part of the table:

CREATE TABLE my_table
(
    int_col INT,        /* integer-valued column */
    str_col CHAR(20),   /* string-valued column */
    date_col DATE       /* date-valued column */
)

Other times data types are implicit, such as when you refer to literal values in an expression, pass values to a function, or use the value returned from that function:

INSERT INTO my_table (int_col,str_col,date_col)
VALUES(14,CONCAT("a","b"),1 9990115)

This INSERT statement performs the following operations, all of which involve data types:

• It assigns the integer value 14 to the integer column int_col.

• It passes the string values "a" and "b" to the CONCAT() function. CONCAT() returns the string value "ab", which is assigned to the string column str_col.

• It assigns the integer value 19990115 to the date column date_col. The assignment involves a type mismatch, so MySQL converts the integer 19990115 to the date "1999-01-15"; this illustrates that MySQL performs automatic type conversion.

To use MySQL effectively, it's essential to understand how MySQL handles data. This chapter describes the types of data that MySQL can handle, and discusses the issues involved in working with those types:

• The general kinds of values MySQL can represent, including the NULL value.

• The specific data types MySQL provides for table columns, and the properties that characterize each column type. Some of MySQL's column types are fairly generic, such as the CHAR string type. Others, such as AUTO_INCREMENT integer types and the TIMESTAMP date type, behave in special ways that you should understand to avoid being surprised.

• Choosing column types appropriately for your tables. It's important to know how to pick the best type for your purposes when you build a table, and when to choose one type over another when several related types might be applicable to the kind of values you want to store.

• MySQL's rules for expression evaluation. MySQL provides a wide range of operators and functions that you can use in expressions to retrieve, display, and manipulate data. The rules for expression evaluation include the rules governing type conversion, when a value of one type is used in a context requiring a value of another type.

  It's important to understand when type conversion happens and how it works; some conversions don't make sense and result in meaningless values. Assigning the string "13" to an integer column results in the value 13, but assigning the string "abc" to that column results in the value 0 because "abc" doesn't look like a number. Worse, if you perform a comparison without knowing the conversion rules, you can do considerable damage, such as updating or deleting every row in a table when you intend to affect only a few rows.

Two appendixes provide additional information about MySQL's column types, operators, and functions. These are Appendix B, "Column Type Reference," and Appendix C, "Operator and Function Reference."