TIME NOW
World current time now,
CALENDAR
Calendar monthly, yearly
login CONVERT LENGTH
login CONVERT TEMPERATURE
login DICTIONARIES, LISTS
login SCIENCE EDUCATION RELIGION
login WORK CALCULATOR
login CALCULATE LIFE

MYSQL dabatase. How to, tutorial, examples

Previous articlePage bottomNext article  ALL TOPICS

Mysql. Data types

 
INTEGER NUMBERS
BIT[(M)]
A bit-field type.
M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted. Length: 1 byte.
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255. Length:  1 bytes
BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true. Length:  1 bytes
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535. Length:  2 bytes.

   MEDIUMINT[(M)] - A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits. Length:  3 bytes.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. INTEGER - This type is a synonym for INT. Length:  4 bytes
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is
-9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. Length:  8 bytes.
NON INTEGER NUMBERS
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale).
The decimal point and (for negative numbers) the “-” sign are not counted in M. If D is 0, values have no decimal point or fractional part.

Max. number of digits (M) = 65.
Max. precision (D) = 30.

If D is omitted, the default is 0.
If M is omitted, the default is 10.

Synonyms:
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
Length:
https://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-characteristics.html

Values for DECIMAL columns are stored using a binary format that packs nine decimal digits into 4 bytes. The storage requirements for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes. The storage required for remaining digits is given by the following table.
 
Leftover Digits Number of Bytes
0 0
1–2 1
3–4 2
5–6 3
7–9 4

A DECIMAL(18,9) column has nine digits on either side of the decimal point, so the integer part and the fractional part each require 4 bytes.

A DECIMAL(20,6) column has fourteen integer digits and six fractional digits.
20 = 14 INTEGER + 6 FRACTION = 9 INTEGER + 5 INTEGER + 6FRACTION = 4Bt (9 INTEGER) + 3Bt (5 INTEGER) + 3 (6 FRACTION) =  10Bt
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Permissible values are -3.402823466E+38, to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.
These are the theoretical limits, based on the IEEE standard.
The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point.
If M and D are omitted, values are stored to the limits permitted by the hardware.

Max. precision (D) = 7 decimal places.
UNSIGNED, if specified, disallows negative values.

Length: 4 bytes.
 
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to - 2.2250738585072014E-308, 0,
and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system. M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is  accurate to approximately 15 decimal places.

Length: 8 bytes.
DATE TIME
DATE
A date.
The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE format, but permits assignment of values to DATE columns using either strings or numbers. Length:  3 bytes.
DATETIME
type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Length:  8 bytes.
TIMESTAMP
type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. Its properties are described in more detail in Section 10.3.1.1, “TIMESTAMP Properties”. Length:  4 bytes.
TIME
Length:  3 bytes.
YEAR
Length:  1 byte
 
STRINGS, TEXT DATA, BIG TEXT
VARCHAR(M), CHAR(M)
The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.
They also differ in maximum length and in whether trailing spaces are retained. The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

Length: CHAR - 255.

VARCHAR(M):  values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
CREATE TABLE t2
(c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
ENGINE = MyISAM CHARACTER SET latin1;

Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535.

Max length: utf8 characters can require up to three bytes per character, (65535 - 2) / 3
 
  CHAR(4) Storage Required VARCHAR(4) Storage Required
'' '    ' 4 bytes '' 1 byte
'ab' 'ab  ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

Links:
CHAR VS VARCHAR: http://dev.mysql.com/doc/refman/5.0/en/char.html
MYSQL table column limits:https://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html
http://stackoverflow.com/questions/332798/equivalent-of-varcharmax-in-mysql
http://stackoverflow.com/questions/8295131/best-practices-for-sql-varchar-column-length

http://stackoverflow.com/questions/16022668/is-it-good-to-store-decimal-value-as-varchar-in-mysql
VARCHAR "11.99" != "11.990"
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
TEXT and BLOB is stored off the table with the table just having a pointer to the location of the actual storage. VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable, the tradeoff of which would be faster depends upon your data and your hardware, you'd want to benchmark a realworld scenario with your data.

Text format messages should almost always be stored as TEXT (they end up being arbitrarily long) String attributes should be stored as VARCHAR (the destination user name, the subject, etc...).

VARCHAR VS TEXT: http://forums.mysql.com/read.php?24,105964,105964

Length:

TINYBLOB, TINYTEXT ~ 256 chars.
BLOB, TEXT ~ 65535 chars.
MEDIUMBLOB, MEDIUMTEXT ~2 mlrd chars.
LONGBLOB, LONGTEXT ~ 500 mlrd chars.
BINARY DATA
BINARY(M), VARBINARY(M)
Length: Same as CHAR and VARCHAR.
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB.
Length: Same as 'TEXT' types.
OTHER DATA TYPES
ENUM('value1','value2',...) 1, 2 bytes
SET('value1','value2',...) 1, 2, 3, 4 bytes 
Previous articlePage topNext article  ALL TOPICS



 Use username: Guest, Anonymous, Programmer






QUOTES:
Women are like teabags. You dont know how strong they are until you put them in hot water.
Eleanor Roosevelt
The heart of the jealous knows the best and most satisfying love, that of the other's bed, where the rival perfects the lover's imperfections.
Djuna Barnes
Happiness and joy always eludes a hypocrite.
Sam Veda