Chapter 3. 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, such as when you issue a CREATE TABLE statement that specifies the type for each column you define 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. The following INSERT statement does all of those things:
INSERT INTO mytbl (int_col,str_col,date_col) VALUES(14,CONCAT('a','b'),20050115);
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:
Two appendixes provide additional information that supplements the discussion in this chapter about MySQL's data types, operators, and functions. These are Appendix B, "Data Type Reference," and Appendix C, "Operator and Function Reference."
The examples shown throughout this chapter use the CREATE TABLE and ALTER TABLE statements extensively to create and alter tables. These statements should be reasonably familiar to you because we have used them in Chapter 1, "Getting Started with MySQL and SQL," and Chapter 2, "MySQL SQL Syntax and Use." See also Appendix E, "SQL Syntax Reference."
MySQL supports several table types, each of which is managed by a different storage engine, and which differ in their properties. In some cases, a column with a given data type behaves differently for different storage engines, so the way you intend to use a column might determine or influence which storage engine to choose when you create a table. This chapter refers to storage engines on occasion, but a more detailed description of the available engines and their characteristics can be found in Chapter 2.
Data handling also depends in some cases on how default values are defined and on the current SQL mode. For general background on setting the SQL mode, see "The Server SQL Mode," in Chapter 2. In the current chapter, default value handing is covered in "Specifying Column Default Values." Strict mode and the rules for treatment of bad data are covered in "How MySQL Handles Invalid Data Values."