BIOS,Windows, Linux, Software,Security, Domains, Web Hosting

MySQL Data Type and Sizes or Ranges

1. MySQL Data Types

There are mainly five data types in MySQL:

Interger Type: BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

Float Type: FLOAT、DOUBLE、DECIMAL

String Type: CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

Date Type: Date、DateTime、TimeStamp、Time、Year

Else Types: BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection

2. MySQL data types and sizes or ranges

Data type Lenght (Bytes) Sizes or ranges
Bit 1 unsigned[0,255], signed[-128,127]
TinyInt 1 interger[0,255]
SmallInt 2 unsigned[0,65535], signed[-32768,32767]
MediumInt 3 unsigned[0,2^24-1], signed[-2^23,2^23-1]]
Int 4 unsigned[0,2^32-1], signed[-2^31,2^31-1]
BigInt 8 unsigned[0,2^64-1], signed[-2^63 ,2^63 -1]
Float(M,D) 4 single-precision float, if D<=24, this field will be float, if D>24, it will be convert to Double
Double(M,D) 8 double-precision
Decimal(M,D) M+1, or M+2 unpackage float, similar as float and double
Date 3 Format as: YYYY-MM-DD, for example 2009-07-19
Date Time 8 Format as: YYYY-MM-DD HH:MM:SS, for example 2009-07-19 11:22:30
TimeStamp 4 Format as: YYYY-MM-DD, for example 2009-07-19
Time 3 Format as: HH:MM:SS, for example 11:22:30
Year 1 Format as: YYYY, for example 2009
Char(M) M
fixed length string
VarChar(M) M variable length string, M must be less than 255, M<=255
Binary(M) M Similar as Char, If its length<M, then 0 will be append in the tail
VarBinary(M) M Similar as VarChar, if its length<M, then 0 will be append in the tail
Tiny Text Max:255 Not case sensitive 
Text Max:64K Not case sensitive 
Medium Text Max:16M Not case sensitive 
Long Text Max:4G Not case sensitive 
TinyBlob Max:255 Case sensitive 
Blob Max:64K Case sensitive 
MediumBlob Max:16M Case sensitive 
LongBlob Max:4G Case sensitive 
Enum 1, or 2 Maximum enum numbers<65535
Set Maximum 8 Max set<64
Geometry    
Point    
LineString    
Polygon    
MultiPoint    
MultiLineString    
MultiPolygon    
GeometryCollection    

3. Recommendations

When you assign a filed data type, minimum principle should be used, If you can use TINY INT, INT should be given up. FLOAT has higher priority than DOUBLE, this will improve mysql efficiency. Another tip, any data table should be oversimplified, a table with so many fields should not be adopted in your project.

If you have more recommendations, just leave them for me without any hesitation. 

Related Articles

Comments(0)  

Leave a Reply

Subscribe to BootBeta Comments RSS feed to receive notification of latest comments posted.

Subscriptions

  • Enter Email Address:

Recent Post

Copyright@ 2010-2014 Bootbeta All Rights Reserved.
HooBlog Ver 1.5 Build 20140530