MySQL Create Table Statements

If you have an existing database table to look at, use these mysql commands to view schema details:


Below are 2 sample CREATE TABLE statements for MySQL.


MySQL Engine
MySQL supports multiple database engines. MyISAM was default in mysql4, InnoDB was default in mysql5. MyISAM is the regular high performance database engine, whereas InnoDB supports database transactions. For more details see either the MyISAM or InnoDB wikipedia articles.

Charset
UTF8 is unicode of course, so it supports international languages, but you can also insert ascii into it, without doing anything special. This of course allows the table to store unicode data. But if you want your database queries to support UTF8, I call mysql_query("SET NAMES 'utf8';"); after I call mysql_select_db().

Choosing your datatypes
MySQL Docs- Overview of Data Types
MySQL Docs- Overview of Numeric Types
MySQL Docs- Field Type Sizes

Keys
Specifying a primary key, is designating which column(s) are to be unique. Usually these are int id fields, and where each record has a unique id. This becomes useful when you are trying to delete or update records in your database.

You can also create other indexes to improve query speed on certain fields with the CREATE INDEX `idx8` on db_table1 (`url`), this is the same as specifying a KEY in the create table script.