MySQL Cheat Sheet

Portions of the following are quoted from the MySQL web site. Observations and opinions expressed below may be useful to MySQL™ newbies (use this information at your own risk).

Use the underscore character in your column names, e.g. first_name. Subsequently in your PHP scripts the underscore can be easily replaced with a space. Use only lower case letters to ensure portability to other system configurations.

Avoid using hyphens in the names of databases, tables, and columns. Situations can arise where the schema is not portable to another system configuration if upper case letters and/or hyphens are used. Hyphens appearing in a double-quoted line of PHP code may be interpreted as a minus sign (crashing the script).

Usage of SET and ENUM fields is tricky when writing PHP scripts, but once mastered, works really well.

MySQL™ will auto-generate sequential record number id's for auto_increment (integer) primary key fields. IMHO, always make an "id" field as the first column (MySQL™ column 0) and make it an auto_increment primary key field (MySQL™ utilities often expect the first column to be an auto_increment field). Vary the name of the field so that you don't end up with a field named "id" in each table (using "id" in every table may cause confusion when working with SQL queries involving more than one table).

Conserve MySQL™ resources and optimize your table schema. 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 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'.

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

If the table being defined does not contain BLOB fields, use CHAR instead of VARCHAR. CHAR is supposedly faster than VARCHAR if the data length is about the same for all records. Note however that you can't mix CHAR & VARCHAR in the same table. MySQL™ will silently convert CHAR fields to VARCHAR fields under certain conditions (such as if a BLOB field is added to the table).

MySQL™ Text Field Limits
Field	Max. Length	Remarks
CHAR(M)	255	Faster than VARCHAR. MySQL™ changes CHAR to VARCHAR when used in the same table with TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT.
VARCHAR(M)	255
TINYTEXT	255
TEXT	65535
MEDIUMTEXT	16,777,215
LONGTEXT	4,294,967,295
An ENUM is a string object whose value normally is chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time. ENUM('X','Y','Z') up to 65,535 values entered can be entered from the list X,Y,Z. A blank value appears to be prepended to the list in phpMyAdmin but does not appear in phpMyEdit files. Usage might include a list of 2-digit U.S. states, where a single value is chosen.
SET is a string object that can have zero or more values, each of which must be chosen from a list of allowed values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (`,'). A consequence of this is that SET member values cannot themselves contain commas. SET('','X','Y','Z') up ~ 64 members can be entered from the list X,Y,Z. Blank value must manually be set at the top of the list. Multiple values can (optionally) be selected from SET's.

Decimals

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. FLOAT(8,2) is good for currency, etc., and requires half the storage capacity of DECIMAL or DOUBLE.

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.

Date / Time Fields

DATE DEFAULT '0000-00-00'

TIME DEFAULT '00:00:00'

DATETIME

DATETIME DEFAULT '0000-00-00 00:00:00'

TIMESTAMP(YYYYMMDDHHMMSS) or TIMESTAMP(YYYY-MM-DD HH:MM:SS)

TIMESTAMP columns are handled differently between v3.x or v4.0 versus v4.1 and v5.0

YEAR(2)

YEAR(4)

Note that the formatting of TIMESTAMP columns is handled differently across different versions of MySQL™, with respect to hyphens and colons used to separate elements.

Whole Numbers
Field	Max. Length	Pos/Neg	Bytes	Low Value	High Value
TINYINT	3	SIGNED	1	-128	+127
TINYINT	3	UNSIGNED	2	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	10	-2,147,483,647	+2,147,483,647
INT	10	UNSIGNED	10	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

MySQL Utilities

Popular utilities for working with MySQL™ include MySQL Administrator, phpMyAdmin, and phpMyEdit. 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).

Before you leave this site, please check out phpMyEdit, MySQL Table Editor.

Search and Replace Text in MySQL

UPDATE `tableName`
SET `ColumnName` = replace(`ColumnName`, 'OldString', 'ReplacementString');

UPDATE `tableName`
SET `content` = replace(`content`, '<h3', '<h1');

UPDATE `tableName`
SET `content` = replace(`content`, '/h3>', '/h1>');

Example of an ALIAS query

The following is an example of aliasing multiple tables, to query site names begining with "$ltr" and associating related articles and authors.

// Name the tables using $A, $B, $C, $D
$A = 'site_name'; // A.site_id, A.site_name, A.town_city, ...

$B = 'site_link'; // B.site_id, B.title_id

$C = 'articles'; // C.title_id, C.material, C.title, ...

$D = 'author_link'; // D.title_id, D.author_id

$qry = "SELECT A.site_id, A.site_name, ".
         ."B.title_id, B.site_id, ".
         ."C.title_id, C.title, ".
         ."D.title_id, D.author_id "
      ."FROM $A A, $B B, $C C, $D D "
      ."WHERE site_name REGEXP '^[$ltr]' ".
         ."AND A.site_id = B.site_id ".
         ."AND B.title_id = C.title_id ".
         ."AND C.title_id = D.title_id "
      ."ORDER BY A.site_name, C.title";

The following is an example of aliasing multiple tables, to query site names begining with "$ltr" to locate records that DO NOT have related articles and authors.

$qry2 = "SELECT $A.site_id, $A.site_name "
         ."FROM $A LEFT JOIN $B ON $A.site_id = $B.site_id "
         ."WHERE $A.site_name REGEXP '^[$ltr]' "
            .'AND $B.site_id IS NULL "
         ."ORDER BY $A.site_name ";

Miscellaneous PHP Examples

phpMyAdmin or MySQL Administrator are probably the most reliable utilities for exporting MySQL data. The following examples create a batch file that can be used with mysqldump.exe on a Windows PC to export data and move the .SQL file into a .GZ archive.

Script 1 creates a batch file used to export MySQL data.

Script 2 moves the exported .SQL file to a .GZ archive.

Sample output from the batch file created using Script 1.

Old example: How to use mysqldump.exe with a batch file in order to backup a MySQL database on Windows.

PHP Script Example - FULLTEXT Search of MySQL Database Table

Related MySQL info can be found on this web site under Reserved Words, MySQL Schema I, MySQL Schema II, and MySQL Image Tables - Inserting images into a MySQL database table.

Upgrade headache with MySQL v3 or v4.0 --> v4.1 or v5

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. Because I have 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.

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 instruction in the prior link. Sometimes Firefox won't let go of old content.

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

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

Example script for uploading images to MySQL

Update: 16 July 2006 - mysql schema and upload form to insert images into mysql

The above script should give you something like this:

mysql schema

Connecting To Multiple MySQL Databases Or Hosts

If using multiple databases, "db." or "db_" may be a good choice for the prefix applied to the name of your scripts and/or web forms. Before including your db connection file, you might add a new variable:

$opts['data_source'] = 1;

In an include file containing the database connection, a switch() statement could be added to resolve the connection:

switch($opts['data_source'])
{
   Case 2:
      $opts['hn'] = '127.0.0.1'; // MySQL hostname or IP address
      $opts['db'] = 'db_name_2'; // MySQL database
      $opts['un'] = 'db_name_2_usr'; // MySQL user
      $opts['pw'] = 'db_name_2_pwd'; // MySQL password
      break;
   Case 1:
   default:
      $opts['hn'] = 'localhost'; // MySQL hostname or IP address
      $opts['db'] = 'db_name_1'; // MySQL database
      $opts['un'] = 'db_name_1_usr'; // MySQL user
      $opts['pw'] = 'db_name_1_pwd'; // MySQL password
      break;
};

Web Developer Resume

phpMyEdit

php-form-generator.com

theirdreams.com

Web Development with PHP and MySQL