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 mytbl  (     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 a function:

 INSERT INTO mytbl (int_col,str_col,date_col)  VALUES(14,CONCAT('a','b'),20020115); 

The INSERT statement shown here 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 20020115 to the date column date_col. The assignment involves a type mismatch, so MySQL performs an automatic type conversion that converts the integer 20020115 to the date '2002-01-15'.

To use MySQL effectively, it's essential to understand how MySQL handles data. This chapter describes the types of data values 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 pro perties that characterize each column type. Some of MySQL's column types are fairly generic, such as the BLOB 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.

• MySQL support for working with different character sets.

• 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 that come into play 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 to supplement the discussion here about MySQL's column types, operators, and functions. These are Appendix B, "Column Type Reference," and Appendix C, "Operator and Function Reference."

The examples used throughout this chapter use CREATE TABLE extensively. The statement should be reasonably familiar to you because we used it in the tutorial section of Chapter 1, "Getting Started with MySQL and SQL." See also the entry for CREATE TABLE in Appendix D, "SQL Syntax Reference." Several examples also use ALTER TABLE to modify the structure of tables. This statement too is discussed in the appendix as well as in Chapter 3, "MySQL SQL Syntax and Use."

MySQL supports several table types, which differ in their properties. In some cases, the way you use a particular column type will be determined or influenced by the table type. This chapter refers to table types on occasion, but a more detailed description of the available types and their characteristics can be found in Chapter 3.