MySQL Cheat Sheet

MySQL™ is a free Open Source database technology found on most Internet web servers.

Two MySQL™ table types are MyISAM and INNODB. MyISAM type tables are most often used for website development. INNODB type tables are more complex because they support transactions and cascading used by advanced web applications which are typically not utilized by the average web developer.

MySQL™ web development often involves using PHP scripting language.

MySQL™ Naming Conventions

While the hyphen (-) may be a valid character in the names of databases, tables, and columns, don't use a hyphen when naming anything. Use of hyphens in the name of a database, table, or column may cause problems. If a hyphenated resource name appears in a double-quoted ("...") string of PHP code, the hyphen will be interpreted as a minus sign and crash the script.

Varied system configurations may cause portability issues if upper case letters are used when naming MySQL™ resources. Use lower case letters to ensure portability to other system configurations. Lower case letters a-z, numeric digits 0-9, and the underscore character are preferred by most users. If using upper case letters, your code might not be portable to a different server, most notably when migrating from Linux to Windows.

Avoid using Reserved Words when naming columns, tables, and databases.

Consider using the underscore character in your column names, e.g. first_name and not FirstName or firstname. Subsequently in PHP scripts the underscore can be easily replaced with a space when displaying the column name in reports.

Prefix the names of your tables with your initials and the underscore character, or another arbitrary prefix. Later on, if you install a different web application in the same database, the possibility of table name conflict is significantly reduced.

Upgrade MySQL™ Version on Windows

I have more than 100 MySQL™ databases and thousands of tables installed on several Windows PC's. Each client's website is mirrored locally on my Windows PC so that testing can be easily accomplished without interfering with the production server.

My experience is that on a Windows PC, there isn't an upgrade option for MySQL™ . You should always export your databases prior to installing a new version of MySQL™ preferably using a MySQL™ utility such as MySQL™ Workbench. And then import the data files after the new MySQL™ version is installed on Windows.

When moving from 32-bit Windows XP to 64-bit Windows 7, if the so-called old password format is in effect, the best thing to do is export your data, verify the export files are readable, backup them up, uninstall MySQL™ , rename the old installation directory, reboot, install the current 64-bit version of MySQL and import data which was exported earlier. Do not import tables `mysql` and `information_schema`. Manually re-enter all the user names, passwords, and privileges using MySQL™ Administrator or MySQL™ Workbench. See OK packet 6 bytes shorter than expected.

Storing NULL instead of an empty string

Perhaps I'm missing something, but the only time I've found it practical to actually store a NULL value in MySQL™ is when the entry of a date field is optional. Otherwise, it makes no sense to me to store NULL when storing an empty string (nothing) conserves resources and avoids adding complexity to queries related to that field. Assign the field attribute NOT NULL unless you have a specific reason for inserting NULL's.

UTF8 Collation and Connection with MySQL™

Specifying a database collation as utf8_generic_ci, using appropriate META tags, and issuing queries for SET NAMES UTF8 and SET COLLATION_CONNECTION=UTF8_GENERAL_CI after the database connection seems to work well for web pages which may contain accented characters.

$cfg['hn'] = 'localhost';
$cfg['db'] = 'database';
$cfg['un'] = 'table';
$cfg['pw'] = 'password';
if(!$cfg['dbh'] = @mysql_connect($cfg['hn'], $cfg['un'], $cfg['pw'])){
   die(mysql_error());
}
if(!@mysql_select_db($cfg['db'], $cfg['dbh'])){
   die(mysql_error());
}
if(function_exists('mysql_set_charset')){
   @mysql_set_charset('utf8', $cfg['dbh']);
}elseif(!@mysql_query('SET NAMES UTF8', $cfg['dbh'])){
   die(mysql_error());
}
if(!mysql_query('SET COLLATION_CONNECTION=UTF8_GENERAL_CI', $cfg['dbh'])){
   die(mysql_error());
}

If using UTF-8, LATIN2 (ISO-8859-2), or CP1250 (WINDOWS-1250) character sets, after connecting to MySQL, and after selecting database, but before querying a table, you may need to execute the appropriate query below.

// UTF8 connection
if(!@mysql_query('SET NAMES UTF8')){ die(mysql_error()); }
if(!@mysql_query('SET COLLATION_CONNECTION=UTF8_GENERAL_CI')){ die(mysql_error()); }

// ISO-8859-2 connection
if(!@mysql_query('SET NAMES LATIN2')){ die(mysql_error()); }
if(!@mysql_query('SET COLLATION_CONNECTION=LATIN2_GENERAL_CI')){ die(mysql_error()); }

// WINDOWS-1250 connection
if(!@mysql_query('SET NAMES CP1250')){ die(mysql_error()); }
if(!@mysql_query('SET COLLATION_CONNECTION=CP1250_GENERAL_CI')){ die(mysql_error()); }

Forcing UTF-8 Connections in MySQL 5.x or higher can potentially be accomplished using a statement in the my.cnf file calling init_connect.

With UTF8, you also need to be sure to apply the appropriate HTML META tags to your document.

Other Headaches

You might upgrade your MySQL™ version to 4.1 or 5.x on your Windows PC, and when you try to connect to the database you are greeted with Client does not support authentication protocol requested by server; consider upgrading MySQL™ client. Be sure to clear your browser cache after following the instructions in the above link. Sometimes browsers won't let go of old content. You may want to close your browser after dumping the cache, to avoid time wasting browser bugs.

It may be that all you need to do is add "old-passwords" to your my.ini file and restart MySQL™.

[mysqld]
...
old-passwords
...

MySQL™ Schema Tips

If you're looking for MySQL™ data to experiment with, download the Sakila schema.

Conserve MySQL™ resources and optimize your table schema. Using INT(10) to store a number which will never exceed 32,767 is wasteful and should be avoided to conserve storage space and maximize efficiency. If a numeric field is going to contain values between 0 and 32,767 then define the column as UNSIGNED SMALLINT(5).

Apply an INDEX to any column which often appears in a WHERE clause or in an ORDER BY clause. Prefix your Index Name / Keyname with a few characters such as 'idx_' to avoid potential problems with ambiguity. The name of the index should be different from the name of the column. When created using phpMyAdmin, the Index Name often defaults to that of the column name, however the Index Name / Keyname should be altered so that it is different from that of the column.

IMHO, always make an "id" field as the first column (MySQL™ column 0) and make it an auto_increment primary key field. Vary the name of the primary key column so that you don't end up with a field named "id" in every table. Using "id" as the name of the key column in every table may cause confusion when working with SQL queries involving more than one table.

Assign the field attribute NOT NULL unless you have a specific reason for inserting NULL's.

MySQL™ Date and Time Column Storage Requirements>

Date / Time Fields	Bytes
DATE DEFAULT '0000-00-00'	3
TIME DEFAULT '00:00:00'	3
DATETIME	6
DATETIME DEFAULT '0000-00-00 00:00:00'	6
TIMESTAMP(YYYYMMDDHHMMSS) or TIMESTAMP(YYYY-MM-DD HH:MM:SS)	4
TIMESTAMP columns are handled differently between v3.x or v4.0 versus v4.1 and v5.0	4
YEAR(2)	1
YEAR(4)	1

MySQL™ TIMESTAMP observations

Several years ago, I had MySQL™ v4.0 on my PC and my web host upgraded to 4.1x, certain queries are returning unexpected results when run on the web. The TIMESTAMP column is handled differently between version 4.0 and 4.1 or 5.0, leaving me to conclude that as regards portability, storing PHP's TIME() value in a VARCHAR(10) column makes more sense than using TIMESTAMP columns. Hyphens are used to separate elements of the TIMESTAMP beginning with 4.1.

Whole Numbers (Integers)

The smallest suitable column type should be employed whenever possible. Do not use a TINYINT column as the auto_increment primary key field unless you're sure the table will contain fewer than 128 records. Do not use an INT column as the auto_increment primary key field unless the table is actually going to hold a billion records.

Using SMALLINT(5) whenever possible will help reduce the amount of storage space required. Refer to the chart on this page that reflects the impact of defining a column as UNSIGNED. I am guessing that most auto_increment primary key fields should be SMALLINT() UNSIGNED NOT NULL DEFAULT '0' ... or possibly MEDIUMINT() UNSIGNED NOT NULL DEFAULT '0'.

Field	Max. Length	Pos/Neg	Bytes	Low Value	High Value
TINYINT	3	SIGNED	1	-128	+127
TINYINT	3	UNSIGNED	1	0	255
SMALLINT	5	SIGNED	2	-32,768	+32,767
SMALLINT	5	UNSIGNED	2	0	65535
MEDIUMINT	7	SIGNED	3	-8,388,608	+8,388,607
MEDIUMINT	8	UNSIGNED	3	0	16,777,217
INT	10	SIGNED	4	-2,147,483,647	+2,147,483,647
INT	10	UNSIGNED	4	0	4,294,967,295
BIGINT	19	SIGNED	8	-9,223,372, 36,854,775,808	+9,223,372, 36,854,775,807
BIGINT	20	UNSIGNED	8	0	18,446,743, 73,709,551,615

Decimals

FLOAT(8,2) is good for currency, etc., and requires half the storage capacity of DECIMAL or DOUBLE.

FLOAT(M,D) A small (single-precision) floating-point number. Cannot be unsigned. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. The M is the display width and D is the number of decimals. FLOAT without an argument or with an argument of <= 24 stands for a single-precision floating-point number.

DECIMAL(M,D) An unpacked floating-point number. Cannot be unsigned. Behaves like a CHAR column: ``unpacked'' means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the `-' sign, are not counted in M (but space for these are reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If D is left out it's set to 0. If M is left out it's set to 10. Note that in MySQL™ Version 3.22 the M argument had to includes the space needed for the sign and the decimal point.

DOUBLE[(M,D)] A normal-size (double-precision) floating-point number. Cannot be unsigned. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. The M is the display width and D is the number of decimals. DOUBLE without an argument or FLOAT(X) where 25 <= X <= 53 stands for a double-precision floating-point number.

MySQL Text Column Types and Storage Requirements

Field	Max. Length	CHAR type columns are often more efficient and faster than VARCHAR columns. However, you can't mix CHAR & VARCHAR in the same table, and if a TEXT column exists in the same table, MySQL will convert the CHAR column to VARCHAR. See user contributed notes on CHAR at the end of this link.
CHAR(M)	255
VARCHAR(M)	255
TINYTEXT	255
TEXT	65535
MEDIUMTEXT	16,777,215
LONGTEXT	4,294,967,295

INFORMATION_SCHEMA and MySQL™ Improved Extension

MySQL™ version 5+ offers an INFORMATION_SCHEMA database containing extensive information on the configured databases, tables, and fields. However, if you have a lot of databases configured, the response time is horrible unless using the *Improved* connection methods, making it essentially unusable under certain configurations. See the Bug Report.

MySQL™ Improved Extension

It appears INFORMATION_SCHEMA works well only with the MySQL™ Improved Extension. Converting PHP code from the original MySQL™ functions to the Improved functions requires some study and syntax alteration. There are a few functions which were discontinued (there is no mysqli_field_len() function). Two projects which I upgraded to the Improved connection appear to have noticeably faster performance.

Good old SELECT COUNT(*) fails to work as planned. Under mysqli_num_rows() the return value is always 1 even though there may be thousands of records in the table. A workaround to resolve SELECT COUNT(*) appears below. It has something to do with buffered vs. un-buffered queries, anyway:

$res = mysqli_query ($link, 'SELECT COUNT(*) FROM `my_table`', MYSQLI_STORE_RESULT);
$found = mysqli_fetch_assoc($res);
$num_recs = $found['COUNT(*)'];

Database connections are also made in a different fashion when migrating to MySQL™ Improved Extension. After converting one project to the Improved extensions, I felt the effort was worth it since minor performance gains were achieved. Some additional diagnostics are available, e.g. mysqli_warning_count() which uncovered minor issues which would otherwise have gone un-noticed.

Utilities and Personal Observations

For those of you who intend to create a development environment on a Windows PC, WAMP5 will quickly install Apache 2, MySQL™ 5, and PHP 5 (users can optionally select a document root that matches your web server).

Fookes Software offers several terrific Windows software applications including NoteTab (a programmable text editor).

Popular utilities for working with MySQL™ include MySQL™ Workbench, phpMyAdmin, and phpMyEdit

Adminer is a great MySQL™ interface which offers much the same functionality as phpMyAdmin using only 1 script.

dBug.php is a great debugging script for PHP.

Related information at sitepoint.com: Top 10 MySQL Mistakes Made By PHP Developers.

WAMP Configurations

In my humble opininon, Apache 2.2 cannot be installed on 64-bit Windows 7 *and* play well with PHP and MySQL™ . My recommendation if creating a WAMP environment on 64-bit Windows is to use Apache 2.0 and not version 2.2. Either version is installable and works perfectly well independently ... but 2.2 won't play well with PHP and MySQL™ and you'll waste 2-3 days trying to figure out why things aren't working as they should. Some of PHP's DLL files for Windows will not work as intended with Apache 2.2.

Installing MySQL™ , PHP, and Apache individually on Windows will expand your knowledge of how these applications work together. You might want to start here.

MySQL™ MEMORY Table or HEAP Table

Consider using a modified version of this to sort populate a MEMORY table with a multi-dimensional array. It might be easier to populate and query a MEMORY table than it is to use PHP to sort certain multi-dimensional arrays. The following was used to process a client list downloaded from a cPanel WHM interface.


<?php



// 2011-03-21 23:09



// Create a temporary MySQL table, insert selected columns from


// a CSV file, query the temporary table and display modified results.



function drop_temp_table($tb = 'lookup'){


    if(!$res = mysql_query("DROP TEMPORARY TABLE IF EXISTS `$tb`")){

        $ret = "\n".'<p>Cannot drop temporary table `'.$tb.'`:<br>'.htmlentities(mysql_error()).'</p>';


    }else{

        $ret = "\n".'<p>The temporary table `'.$tb.'` has been dropped.</p>';


    }

    return $ret;

};



$buffer = '';



$fn = '../../clients.csv'; // utf-8 encoding




// Fetch 5 of 15 columns in the CSV: 0,1,2,6,7



$stop = 5 + 1; // 5 columns plus column 0 (id column)




$temp_table = 'lookup';



$opts['hn'] = '127.0.0.1';



$opts['db'] = 'test';




$opts['un'] = 'root';



$opts['pw'] = '';




if(!$opts['dbh'] = @mysql_connect($opts['hn'],$opts['un'],$opts['pw'])) {




   die('<p>Cannot connect to MySQL:<br>'.htmlentities(mysql_error()).'</p></body></html>');




}



if(!@mysql_select_db( $opts['db'], $opts['dbh'])){



   die('<p>Cannot select MySQL database:<br>'.htmlentities(mysql_error()).'</p></body></html>');




}



for($i = 0; $i < $stop; $i++){



    $buffer .= "\n".'`col'.$i.'` char(100) not null default "",';




}



// KEY (`col5`) is the future sort column



$qry = <<<HEREDOC_VAR

CREATE TEMPORARY TABLE IF NOT EXISTS `$temp_table` (


`id` smallint(5) unsigned NOT NULL auto_increment, $buffer

PRIMARY KEY (`id`),

KEY (`col5`)

)ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;


HEREDOC_VAR;



if(!$res = @mysql_query($qry)){



    echo "\n".'<p>Cannot create temporary table `'.$temp_table.'`: '.mysql_error().'<br>'.nl2br(htmlspecialchars($qry)).'</p>';




}else{



    echo "\n".'<p>Temporary table created:<blockquote>'.nl2br(htmlspecialchars($qry)).'</blockquote></p>';




    $row = 1;



    if($fh = @fopen($fn, 'r')){




        while(($data = fgetcsv($fh, 1024)) !== FALSE){



            // $num_cols = count($data);




            if($row == 1){



                echo "\n".'<p>row #'.$row.' omitted</p>';




            }else{



                // clean up bad column 6 in CSV file



                $data[6] = str_replace('&nbsp;M', '', $data[6]);




                // calculate percentage of column 7 / 6



                $pct = $data[7] / $data[6];




                $qry = 'INSERT INTO `'.$temp_table.'` 
   VALUES ("", "'.$data[0].'", "'.$data[1].'", "'.$data[2].'", "'.$data[6].'", "'.$data[7].'", "'.$pct.'");';




                if(!$res = mysql_query($qry, $opts['dbh'])){



                    unset($data);




                    echo drop_temp_table();



                    echo "\n".'</body></html>';



                    exit;



                }else{




                    echo "\n".'<p>'.nl2br(htmlspecialchars($qry)).'</p>';




                    //echo "\n".'row #'.$row.' inserted, ';



                }



            }



            $row++;



        } // end while()




        fclose($fh);



        unset($data);



        // query the temp table



        $qry = "SELECT * FROM `$temp_table` ORDER BY `col5` DESC";




        echo "\n".'<br><hr><p>'.nl2br(htmlspecialchars($qry)).'</p>';




        if($res = @mysql_query($qry, $opts['dbh'])){



            $num_rows = @mysql_num_rows($res);




            if(empty($num_rows)){



                echo "\n".'<p>No records were found.</p>';




                echo drop_temp_table();



            }else{



                echo "\n".'<table border="1" cellpadding="5" cellspacing="0" class="forms">'."\n";




                while($ary = @mysql_fetch_assoc($res)){



                    foreach($ary as $key => $val){




                        $$key = stripslashes(trim($val));



                    }



                    echo "\n\n".'<tr>';




                    echo "\n".'<td align="right">'.$id.'</td>';



                    echo "\n".'<td>'.$col0.'</td>';




                    echo "\n".'<td>'.$col1.'</td>';



                    echo "\n".'<td>'.$col2.'</td>';




                    echo "\n".'<td align="center">'.$col3.'</td>';



                    echo "\n".'<td align="right">'.$col4.'</td>';




                    echo "\n".'<td align="right">'.sprintf('%.1f', $col5 * 100).'%</td>';




                    echo "\n".'</tr>';



                }



                echo "\n".'</table>'."\n";




                echo drop_temp_table();



                mysql_free_result($res);



            }



        }else{



            echo drop_temp_table();




            echo "\n".'<p>Query failed: '.nl2br(htmlspecialchars($qry)).'</p>';




        }



    }else{



        echo "\n".'<p>Cannot open '.$fn.'</p>';




        echo drop_temp_table();



    }



}



?>

PHP Form Generator for MySQL™ MyISAM type tables