Apr 14, 2011
Database table holds the actual data, it is the container of ‘records’. A record can have multiple ‘fields’ or ‘columns’. Fields describe a particular ‘property’ of a record. For example, if you will create a database that will contain records of ‘fruits’, perhaps you will provide some information about ‘fruits’. Maybe a ‘name’, how many seeds it has, the color, the shape etc.
Prior to creating your tables, you should know what database you are currently using. And before we define the fields we should know the ‘data type’ of a particular ‘field’. What we want to store in a table determines the ‘data type’ we should use. Below is the list of MySQL Data Types.
Type Name —————– Max Size ————– Storage Space
CHAR(X) ————— 255 bytes ————- X bytes
VARCHAR(X) ———— 255 bytes ————- X+1 byte
TINYTEXT ————– 255 bytes ————- X+1 byte
TINYBLOB ————– 255 bytes ————- X+2 bytes
TEXT —————— 65535 bytes ———– X+2 bytes
BLOB —————— 65535 bytes ———– X+2 bytes
MEDIUMTEXT ———— 1.6MB —————– X+3 bytes
MEDIUMBLOB ———— 1.6MB —————– X+3 bytes
LONGTEXT ————– 4.2GB —————– X+4 bytes
LONGBLOB ————– 4.2GB —————– X+4 bytes
Type Name ————- Memory Space
TINYINT ————- 1 byte
SMALLINT ———— 2 bytes
MEDIUMINT ———– 3 bytes
INT —————– 4 bytes
BIGINT ————– 8 bytes
FLOAT(M,D) ———- 4 bytes
DOUBLE(M,D) ——— 8 bytes
DECIMAL(M,D) ——– The value of M + 2 bytes
Type Name ————- Value Range ———————————- Unsigned
TINYINT ———– -128 to 127 ———————————– 0-255
SMALLINT ———- -32768 to 32767 ——————————- 0-65535
MEDIUMINT ——— -8388608 to 8388607 ————————— 0-16777215
INT ————— -2147483648 to 2147483647 ——————— 0-4294967295
BIGINT ———— -9223372036854775808 to 9223372036854775807 — 0-18446744073709550615
FLOAT(M,D) ——– Varies depending on values
DOUBLE(M,D) ——- Varies depending on values
DECIMAL(M,D) —— Varies depending on values
The CHAR(X), VARCHAR(X), TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT datatypes allows you to store text. But only the CHAR(X) datatype accepts ‘X'(length) of a particular text and store it padded with spaces if the value to be inserted is less than ‘X’. It means that when you specify ‘<field name> CHAR(6)’ in your ‘CREATE TABLE…’ statement, the length of the field values will always be ‘6’ characters length. If you will try to insert more than ‘6’ characters it will be truncated silently. And if you try to insert less than ‘6’ characters it will be padded by spaces. Unlike the VARCHAR type that if you insert more than what is expected it will truncate the value then it will show you a warning. And if you insert less, the value will not be padded by spaces. See image below.
The TINYBLOB, MEDIUMBLOB AND LONGBLOB is used to store binary data usually images, videos etc.
Using FLOAT, REAL and DOUBLE data types if not use carefully can lead to problems. Here’s what MySQL have to say about these data types.
“MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, (M,D) means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.
Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.
For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.”
DATETIME – in the format of ‘YYYY-MM-DD HH:MM:SS’
DATE – in the format of ‘YYYY-MM-DD’
TIME – in the format of ‘HH:MM:SS’
Note: The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. The word ‘supported’ according to the MySQL website mean that although earlier values might work, there is no guarantee.
Now let’s try to create a simple table.
In the video presented earlier, the SQL statement ‘USE <database name>;” allows us to ‘use’ a database when using ‘mysql’ commandline tool. When in MySQL Browser, all you have to do is to ‘double-click’ the database on the list. Although you can also issue SQL statements in the MySQL Browser, it is much easy to use the GUI interface.
And to check for existing tables of a particular database, we use ‘SHOW TABLES;’. And we can also use this to check if the table was ‘really’ created after issuing a ‘CREATE TABLE…’ statement when using the commandline tool. On the next part(Part 3) we will try to populate tables by issuing ‘INSERT’ SQL statement.
If you’ve missed Part 1 just click here -> Install the MySQL GUI Tools