mySQL table types

[iBram007]

Hello guys,

I'm learning mySQL at the moment, togheter with phpmyadmin.
When I want to create a table, I can choose from a whole list of table types:

TINYINT,
SMALLINT,
MEDIUMINT,
INT,
BIGINT,
FLOAT,
DOUBLE,
DECIMAL,
DATE,
DATETIME,
TIMESTAMP,
TIME,
YEAR,
CHAR,
VARCHAR,
TINYBLOB,
TINYTEXT,
TEXT,
BLOB,
MEDIUMBLOB,
MEDIUMTEXT,
LONGBLOB,
LONGTEXT,
ENUM,
SET


Can anyone me explain the differents or give me an url with the differents between this types?
Thanks in advance!

[freakysid]

Your best bet is to read through chapter 7.3 of the manual http://www.mysql.com/doc/C/o/Column_types.html

[Karl]

Just a quick note, these aren't table types, they are column types, the MySQL table types are:

ISAM
MyISAM
HEAP
BDB - Berkeley Database, which is the table type used when you need to support transactions.

[iBram007]

Thanx guys!

Ok, I have that list, but don't understand it

What type do I use for:
- a text block
- numbers (zip codes, telefon#,...)
- address line (street)
- url

[sylow]

For Text you have following options and I took them all from the page freakysid mentioned they look very clear to me.

TINYTEXT
A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters.
MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters.
LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters.

and for the rest I recommend you to check that page again to understand. First check your field values range and write it down and then go to that web page and see which data type is proper for that range.
As an example, zip codes in Nederland includes 4 number + 2 character, in US and Turkey it is 5 digit integer. so I recommend you choose varchar instead of number type.
For address line, I would use varchar(150) it should be enough.
For url varchar(250) is long enough I think.

[freakysid]

Originally posted by iBram007
Thanx guys!

Ok, I have that list, but don't understand it

What type do I use for:
1 a text block
2 numbers (zip codes, telefon#,...)
3 address line (street)
4 url

These are just my suggestions:
1) TEXT

VARCHAR can be up to 255 characters. Anything over this requires a TEXT type. VARCHAR and TEXT types accommodate variable string length, meaning that they will only take up storage according to the length of the string (unlike a fixed length string - the CHAR type.

At the end of the day, the only difference between the variable string length types is the number of bytes needed to store the string length:

VARCHAR - requires 1 byte,
TINYTEXT - requires 1 byte,
TEXT - requires 2 bytes,
MEDIUM TEXT - requires 3 bytes,
LARGETEXT - requires 4 bytes.

Note that TEXT types cannot be indexed while VARCHAR can be indexed (so, for example a TEXT type cannot be part of the primary key).

2) Depends on the type of number

For an integer (whole number) the decision to make is which range of values to support - because the larger the int type the more bytes will be used to store the number. From the manual:

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

For floating point numbers - use FLOAT or DOULBE (again - depending on the range and precision choose between float or doulbe - double is double the size/precision but requires twice the storage space).

Also, is a code a number - usually not! I would not consider a telephone number a number - but a numeric code - so would use a CHAR or VARCHAR type for it. A zip code, on the other hand, you may want to perform mathematical operations on (eg, find all the zip codes within the range of 1000 to 3000). So an SMALLINT UNSIGNED would be OK to use here (assuming that zip codes don't go over approx 65000).

3) VARCHAR(M) - You most likely want to limit the length of the string "M" to some number - say 50 characters - so VARCHAR is suitable.

4) URL - again VARCHAR would be the go here.

[iBram007]

Thanx guys!