Database Tips
Posted: Thu Apr 17, 2008 3:39 pm
Shamelessly stolen from [url=\"http://www.webpronews.com/topnews/2005/06/06/mysql-for-beginners-how-to-create-a-mysql-database\"]here[/url].
After creating a MySQL database, you need to create a table. There are
many data types and choosing the wrong one can create huge problems
later on. This is a simple list of the different data types and what
they are generally used for.
TINYINT: A very small integer (-128 to 127)
SMALLINT: A small integer (-32768 to 32767)
MEDIUMINT: A medium-sized integer (-8388608 to 8388607)
INT: A normal-sized integer (-2147483648 to 2147483647)
BIGINT: A very large integer
FLOAT: A floating-point number
DOUBLE: A double-precision floating-point number
DECIMAL: A packed exact fixed-point number
VARCHAR: Varying Characters (up to 255 characters, no line break)
TEXT: A column (up to 65,535 characters, with line break)
BLOB: A case-sensitive column (up to 65,535 characters, with line break)
DATE: A date
TIME: A time
DATETIME: Date and Time combination
TIMESTAMP: useful for recording the date and time of an INSERT or UPDATE
operation
Auto Increment: Auto-Increment fields are useful for assigning unique
identification numbers for users, products, and customers, etc. By
default, fields are incremented using number characters (like "1", "2").
Primary Key: The primary key is a data column that uniquely identifies a
specific instance of that data. At least one of your fields must be a
Primary Key. Username is an example of a good primary key. You do not
want to have more than one individual having the same username.
Index Key: Allows you to speed up searches by designating a field as a
preferred data source, especially when combining data from multiple
tables.
After creating a MySQL database, you need to create a table. There are
many data types and choosing the wrong one can create huge problems
later on. This is a simple list of the different data types and what
they are generally used for.
TINYINT: A very small integer (-128 to 127)
SMALLINT: A small integer (-32768 to 32767)
MEDIUMINT: A medium-sized integer (-8388608 to 8388607)
INT: A normal-sized integer (-2147483648 to 2147483647)
BIGINT: A very large integer
FLOAT: A floating-point number
DOUBLE: A double-precision floating-point number
DECIMAL: A packed exact fixed-point number
VARCHAR: Varying Characters (up to 255 characters, no line break)
TEXT: A column (up to 65,535 characters, with line break)
BLOB: A case-sensitive column (up to 65,535 characters, with line break)
DATE: A date
TIME: A time
DATETIME: Date and Time combination
TIMESTAMP: useful for recording the date and time of an INSERT or UPDATE
operation
Auto Increment: Auto-Increment fields are useful for assigning unique
identification numbers for users, products, and customers, etc. By
default, fields are incremented using number characters (like "1", "2").
Primary Key: The primary key is a data column that uniquely identifies a
specific instance of that data. At least one of your fields must be a
Primary Key. Username is an example of a good primary key. You do not
want to have more than one individual having the same username.
Index Key: Allows you to speed up searches by designating a field as a
preferred data source, especially when combining data from multiple
tables.