MySQL Syntax

Although MySQL supports most of the standard SQL syntax discussed in Chapter 18, there are those things that are slightly different, unusual, or enhanced. In this section, we will cover some of the basic rules you should keep in mind as you begin to develop your application using MySQL as the back-end database server.

Strings and Identifiers

MySQL allows you to delimit strings with either single quote ( ' ) or double quote ( " ) characters. Either of the following is a valid string for MySQL: 'Dreamweaver MX' or "Dreamweaver MX".

Tip 

MySQL also supports special characters in strings by means of an escape sequence using a backslash ( \ ) as the escape character. Among the special escape sequences you may need to use are \' (to represent a single quote within a string) and \" (to represent a double quote within a string). It is good practice to scan character strings within your web application for characters with special meaning and escape them as appropriate before attempting to store the value in the database. For a complete listing of these escape sequences see www.mysql.com/doc/en/String_syntax.html.

You should exercise caution when naming identifiers within MySQL. Identifiers include table names, column names and database names. In general, MySQL is very flexible about the usage of special characters and even "reserved" words within or as identifiers. Database, table, and column names have a maximum length of 64 characters. MySQL allows database and table names to contain any character that your operating system will allow in a filename with the exception of '/' and '.' (Database names also must not contain a '\'). MySQL will allow identifiers to have the name of what would otherwise be considered a reserved word. The only thing you must remember is that if your identifier is a MySQL reserved word, you have to quote it with a ( ` ) back tick character when you use it in your SQL statements. For example, "table" is a reserved word in MySQL (used when defining or changing a database table), however you are permitted to use a table that has the name "table" as in the following:

mysql> SELECT * FROM `table`;  +----------+------------+---------------------+  | table_id | table_name | table_description   |  +----------+------------+---------------------+  |        1 |      table | a listing of tables |  |        2 |      users | system users        |  +----------+------------+---------------------+ 

Be careful, though, if you forget that you have used a reserved word and do not quote it with a back tick, your application is bound for trouble:

mysql> SELECT * FROM table; ERROR 1064: You have an error in your SQL  syntax near 'table' at line 1

Warning 

In practice, we would recommend that you avoid using identifiers that are reserved words in MySQL. While you are able to, your application may not be as easily ported to other database systems if that becomes necessary and your chances of encountering syntax problems, like those mentioned above, is greatly increased.

Case Sensitivity

MySQL, in general, is not case sensitive when it comes to structuring your SQL statements. The following will return the same results:

mysql> select * from  mytable; mysql> SELECT * FROM mytable;

Because MySQL utilizes standard operating system files to contain the definition of databases and tables, it is dependent upon the underlying operating system to determine whether database names and table names are case sensitive. On Windows platforms, database and table names are not case sensitive, but on most Unix systems, database and table names are case sensitive. In other words, on Windows the tables "mytable" and "MyTABLE" reference the same table, but on Unix, creating a table as mytable and then referencing it as MyTABLE will result in an error because MySQL will be unable to find the table MyTABLE.

Tip 

To minimize errors and increase the likelihood that your application will run against a MySQL server on either Windows or Unix, you would be well advised to consistently name your tables and reference them using the same case throughout the application.

Useful Statements

Obviously, the whole idea of creating a database-driven web application is to manipulate a set of data somewhere! To that end, you will find yourself using the basic SQL commands (SELECT, INSERT, UPDATE and DELETE) quite a bit. There are also some differences (or extensions) available to MySQL users. One notable extension to the SELECT statement is the ability of MySQL to limit the rows returned by a query. This is extremely valuable when working with a web application that requires a user to go through large amounts of data with a "screen at a time" approach (also called paging through the data).

SELECT … LIMIT

To easily page through rows of data from your web application, you will find the LIMIT clause of the SELECT statement most useful. This allows you to specify a starting row and the number of rows to return in the result set. The syntax of the LIMIT clause is LIMIT [startingrow,] rowcount where the optional startingrow represents the starting row (if not specified MySQL starts with the first row of data) and rowcount represents the total number of rows to return. For example, if you have a table containing authors and titles for 300 books and you want to allow the user of your web application to page through these titles in groups of 20 you could use the following:

mysql> SELECT author, title FROM books LIMIT 0, 20

Providing the user with "Next" and "Previous" links from within your web application is as simple as keeping track of the starting record that was just used and issuing statements for the next 20 rows and the previous 20 rows. After using the above statement, to get the next 20 rows of data the following would be used:

mysql> SELECT author, title FROM books LIMIT 20, 20

Because the starting row defaults to the first row of data, if you want to retrieve the first 30 rows of data you could simply use:

mysql> SELECT author, title FROM books LIMIT 30

Finally, you can use the special value -1 as the rowcount parameter which tells MySQL to start at the specified row and return as many rows as are left.

mysql> SELECT author, title FROM books LIMIT 295, -1

Warning 

For purposes of the LIMIT clause, MySQL counts the rows starting at 0 for the first row instead of 1. A statement of: SELECT author, title FROM books LIMIT 10, 20 tells MySQL to start at the 11th row and return the next 20 rows.

LOAD DATA INFILE

LOAD DATA INFILE allows you to quickly populate a table in MySQL (or bulk add rows to an already populated table). Before you can use this statement you must have "file" privileges on the MySQL server. The MySQL privilege and security system is complex and beyond the scope of this book, please refer to your MySQL system administrator or the online documentation for further details about MySQL privileges assigned to you.The basic syntax of this statement is:

LOAD DATA INFILE 'MyFile.txt' INTO TABLE MyTable;

This will cause the server to read the file MyFile.txt and copy the rows of data in the table MyTable. By default, MySQL assumes that individual fields in the file are separated by a tab character, individual fields are quoted with single quotes ( ' ), special characters are escaped with a ( \ ) and lines are terminated with a new line character.

In Unix the new line is a single character: ASCII 10 or Line Feed. In Windows, a pair of characters represents the new line: ASCII 13 followed by ASCII 10 or Carriage Return and Line Feed. When importing text files created on Windows you'll need to override the line termination value with the optional clause LINES TERMINATED BY.

A more common file would be one of comma separated values where text fields are quoted with double quotes ( " ) and lines are terminated with the Windows carriage return/line feed pair. The statement to load that file would look like this:

LOAD DATA INFILE 'MyFile.txt' INTO TABLE  MyTable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED  BY '"' LINES TERMINATED BY '\r\n'; 

For a complete discussion of the LOAD DATA INFILE along with numerous examples see the online documentation at www.mysql.com.

For More Information

There are a vast number of Internet resources available for further education on the subject of MySQL and a larger number of sites that rely on MySQL to support their own web applications. Check out MySQL's site at www.mysql.com. Or, browse through any of the tutorials on WebMonkey at www.webmonkey.com or simply do a search for MySQL at Google, Yahoo, or your favorite search engine. You'll find many resources available to you. One of the best sources of information about all aspects of MySQL is Ian Gilfillan's Mastering MySQL (Sybex, 2002).