phpBB

Development Wiki

Best Practices: MySQL

From phpBB Development Wiki

(Redirected from Best Practices:mySQL)

Field Type

SIGNED vs UNSIGNED

SIGNED should be used where a field might contain a negative number such as distances
UNSIGNED should be used where a field will never contain a negative number such as unix timestamp or id fields

Use the correct size

You should always use the correct field type for the size of the value you will be inputting into it.

Type Minimum Maximum
SIGNED TINYINT -128 127
UNSIGNED TINYINT 0 255
SIGNED SMALLINT -32,768 32,767
UNSIGNED SMALLINT 0 65,535
SIGNED MEDIUMINT -8,388,608 8,388,607
UNSIGNED MEDIUMINT 0 16,777,215
SIGNED INT -2,147,483,648 2,147,483,647
UNSIGNED INT 0 4,294,967,295
SIGNED BIGINT -9,223,372,036,854,775,808 9,223,372,036,854,775,807
UNSIGNED BIGINT 0 18,446,744,073,709,551,615

CHAR vs VARCHAR

CHAR should be used when a fixed number of character is expected such as an MD5 has which is always 32 characters.
VARCHAR should be used when an unknown number of characters is expected such as a name or email address

Character vs Numerical

When ever possible you should be storing a numerical value in a numeric field type and not a character field type.
For example dates and time can be stored as a unix timestamp. IP address can also be stored as an INT by using the ip2long()