Type of data supported by MySQL

In this article we will discuss types - data types supported by MySQL . Selection of data types is a matter that is important in managing the server. One reason is related to the space on my hard drive and memory to be " used " by these data .

The following will be given the data types supported by MySQL are fetched from the MySQL documentation . Type - type of data is provided in a form ready to be written in the syntax - syntax MySQL , for example Create Table. In these data types , there are several attributes that have the following meanings :

• M , shows a broad maximum characters . The maximum F value is 255 .
• D , shows the number of decimal places . The maximum value of D is 30 but is limited by the value of M , which must not be larger than the M - 2 .
• Attributes that are marked [ and ] means that its use is optional .
• If the attribute ZEROFILL included, MySQL will automatically add the attribute unsigned .
• are unsigned numbers without a sign in front of it ( eg a negative sign ) .

These are the types of data:

• TINYINT [( M ) ] [ unsigned ] [ ZEROFILL ]

a very small range of integer values , ie -128 to 127 . unsigned range is 0 to 255 .

• SMALLINT [( M ) ] [ unsigned ] [ ZEROFILL ]

small integer value range , ie up to 32 767 -32 768 . unsigned range is 0 hinga 65535.

• MEDIUMINT [( M ) ] [ unsigned ] [ ZEROFILL ]

Integer secondary level . value range is -8388608 to 8388607 . unsigned range is 0 to 16,777,215 .

• INT [( M ) ] [ unsigned ] [ ZEROFILL ]

normal -sized integer . value range is -2147483648 to 2147483647. unsigned range is 0 to 4294967295 .

• INTEGER [( M ) ] [ unsigned ] [ ZEROFILL ]

Same as INT .

• BIGINT [( M ) ] [ unsigned ] [ ZEROFILL ]

large integer . value range is -9223372036854775808 to 9223372036854775807 . unsigned range is 0 to 18446744073709551615 .

• Float ( precision ) [ ZEROFILL ]

floating-point numbers . can not be unsigned . precision attribute value is < = 24 for the number of single precision floating-point and between 25 and 53 for floating -point double precision .

• Float [( M , D ) ] [ ZEROFILL ]

Numbers of single precision floating-point . can not be unsigned . Permitted values are - 3.402823466E +38 to - 1.175494351E - 38 for negative values , 0 , and 1.175494351E - 38 to 3.402823466E +38 for positive values.

• DOUBLE [( M , D ) ] [ ZEROFILL ]

Floating -point double precision . can not be unsigned . Permitted values are - 1.7976931348623157E +308 to - 2.2250738585072014E - 308 for negative values , 0 , and 2.2250738585072014E - 308 to 1.7976931348623157E +308 for positive values.

• DOUBLE PRECISION [( M , D ) ] [ ZEROFILL ] and REAL [( M , D ) ] [ ZEROFILL ]

Both are the same as DOUBLE .

• DECIMAL [( M [, D ] ) ] [ ZEROFILL ]

floating-point numbers are " unpacked " . can not be unsigned . mirit properties with CHAR . The word " unpacked ''means the number is stored as a string , using one character for each digit . Coverage equal to the value of DECIMAL DOUBLE , but it also depends dai attribute values of M and D are included. If D is not filled will be considered 0 . If M is not filled it will be considered 10 . Since MySQL 3:22 M value should include the space occupied by the numbers behind the comma and the sign + or -.

• Numeric ( M , D ) [ ZEROFILL ]

Same as DECIMAL .

• DATE

A date . MySQL displays the date in the format ' YYYY - MM- DD ' . value range is '1000 - 01-01 ' to '9999 - 12-31 ' .

• Datetime

A combination of time (hours ) and date. MySQL displays time and date in the format ' YYYY - MM- DD HH : MM : SS ' . value range is '1000 - 01-01 00:00:00 ' to '9999 - 12-31 23:59:59 ' .

• TIMESTAMP [( M ) ]

A timestamp . Its scope is from '1970 - 01-01 00:00:00 ' to sometime in the year 2037 . MySQL displays TIMESTAMP data type in YYYYMMDDHHMMSS , YYMMDDHHMMSS , YYYYMMDD , or YYMMDD , depending on the value of M , if 14 ( or not written ) , 12 , 8 , or 6 .

• TIME

time data types . Its scope is' -838:59:59 ' to '838 : 59:59 ' . MySQL displays TIME in the format 'HH : MM : SS ' .

• YEAR [( 2 | 4 ) ]

Figures for the year , in the format of 2 - or 4 - digit (default is 4 - digits). Possible values are 1901 to 2155 , 0000 on four - digit format , and from 1970 to 2069 on two - digit format ( 70-69 ) .

• CHAR ( M ) [ BINARY ]

The string has fixed width . The value of M is from 1 to 255 characters. If there is left over is filled with spaces ( eg the value of M is 10 , but the data is stored only has 7 characters , then the three remaining characters filled with spaces ) . Spaces will be eliminated if the data is called. Value of CHAR will be sorted and compared in case-insensitive by default character set is available , unless the BINARY attribute is included.

• VARCHAR ( M ) [ BINARY ]

String with wide variation. The value of M is from 1 to 255 characters. If the value of M is 10 while the data is stored only consists of five characters, then the width of the data is only 5 characters only, no additional spaces.

• TINYBLOB and TINYTEXT

A BLOB (sort of record) or TEXT with a maximum width of 255 ( 2 ^ 8-1 ) characters.

• BLOB and TEXT

A BLOB or TEXT with a maximum width of 65 535 ( 2 ^ 16-1 ) characters.

• MEDIUMBLOB and MEDIUMTEXT

A BLOB or TEXT with a maximum width of 16777215 ( 2 ^ 24-1 ) characters.

• LONGBLOB and LONGTEXT

A BLOB or TEXT with a maximum width of 4294967295 ( 2 ^ 32-1 ) characters.

• ENUM (' value1 ', 'value2 ',...)

An enumeration , which is a string object can have only one value , chosen from a list of values' value1 ',' value2 ' , ..., NULL or the special " " error . An ENUM can have a maximum of 65 535 type rating.

• SET (' value1 ', 'value2 ',...)

A set , namely a string object that can have 0 or more values , which must be chosen from a list of values' value1 ',' value2 ' , .... A SET can have a maximum of 64 members .

Now, by knowing the data types supported by MySQL , you can accurately choose the type of data you need in compiling a database .

Suppose you want to save the data on the number of stocks that do not exceed the number 200 for example , then you should choose a given data type TINYINT unsigned attribute . The reason is the number of stocks does not exceed 200 and can not be smaller than 0 , and TINYINT data type has a range of 0 to 255 , so it qualifies for use .

Another example , say you want to create a database of phone numbers from your friends . It consists entirely of phone number digits , but it seems inappropriate when stored in the INT data type ( or even a BIGINT if you want to save the phone number ) . Better incorporated into CHAR or VARCHAR with M is 12 . Why 12 ? Because the longest mobile phone numbers consist of 12 digits. While the longest home phone number is eight digits plus the longest four -digit area code , so the number 12 is very fitting .

Then how its application in the operation of MySQL ? Suppose you want to create a table with the inventory of each field is the item code , item name , price , supplier , and date of purchase , then it is likely that the command should be written is as follows :

mysql > create table stock (
- > code char ( 5 ) ,
- > name varchar ( 20 ) ,
- > price mediumint unsigned ,
- > supplier char ( 5 ) ,
- > date date) ;

The above command will create a table called stock with the following fields :

Field name	Data type	Information
Code	Char ( 5 )	Code of goods usually have a fixed number of characters , in this example is 5 .
Name	Varchar ( 20 )	Name of goods likely to have a number of different characters , so it is appropriate to use varchar .
Price	Unsigned Mediumint	Price depends on the type of goods , in this example is considered the highest prices under 100 millions .
Supplier	Char ( 5 )	Suppliers are usually also written in a certain code that the number of permanent character .
Date	Date	Date of purchase. Generally do not need to purchase time is included, only the date only.

Well , already explained above, type - type of data operation the MySQL database server, good luck : D. The next article will discuss the implementation of the PHP connection to MySQL database .