MySQL data table type data type

Source: http://blog.chinaunix.net/uid-10064369-id-2971161.html Table TypeMySQL has a lot of data table types, the more important of which is the myisam,innodb of these two kinds. Each of these two types has advantages and disadvantages, need to choose the appropriate according to the actual situation, MySQL support for different tables set different types. Here's a comparison: the MyISAM table type is a fairly mature and stable table type, but MyISAM does not support some features.

	MyISAM	InnoDB
Transaction	Not supported	Support
Data row Locking	Not supported, only table locked	Support
FOREIGN KEY constraints	Not supported	Support
Table Space Size	Relatively small	Relatively large, maximum is twice times
Full-Text Indexing	Support	Not supported
GIS data	Support	Not supported
Count problem	No	Slow when executing a count (*) query

In general, I think it's OK to choose MyISAM, if you need a transaction, or if you need a lot of users to modify data in a table at the same time, consider innodb data table. Data Type1. Integral type (xxxint)

MySQL data type	Meaning
tinyint (m)	1 byte representation ( -128~127)
smallint (m)	2 byte representation ( -32768~32767)
Mediumint (M)	3 byte representation ( -8388608~8388607)
Int (m)	4 byte representation ( -2147483648~2147483647)
BigInt (M)	8-byte representation (18 +-9.22*10 of the X-square)

The value range on the right is in the case where the unsigned keyword is not added, if unsigned is added, the maximum value is doubled, such as the tinyint unsigned value range (0~256). The book says that the m in brackets is the display width in the Select query result set, does not affect the actual range of values, I measured it, defined a field number type int (4), insert a record "123456", and use MySQL query Broswer executes the select query, and the returned result set 123456 is displayed correctly, without affecting the width of the display and not knowing what this m does. 2. Floating-point type (float and double)

MySQL data type	Meaning
Float (m,d)	Single-precision floating point, 8-bit precision (4 bytes), M is the total number of decimal digits, D is the number of digits after the decimal point.
Double (m,d)	Double-precision floating-point, 16-bit precision (8 bytes)

The parameter M only affects the display effect, does not affect the precision, but the D is different, will affect the precision. For example, a field is defined as float (5,3), if you insert a number 123.45678, the actual database is 123.457, the number after the decimal point is rounded to 457, but the total number is not limited (6 bits, more than the defined 5 bits). 3. Fixed-point (decimal) decimal (m,d) fixed-point type floating-point types hold an approximate value in the database, while the fixed-point type holds the exact value in the database. The parameter m is the maximum number (precision) of a fixed-point type number with a range of 0~65,d digits to the right of the decimal point and a range of 0~30, but not more than M. The calculation of the fixed-point number can be accurate to 65 digits. 4. String (Char,varchar,xxxtext)

mysql data type	meaning &N BSP;
char (n)	fixed-length string with a maximum of 255 characters
varchar (n)	fixed String of length, up to 65,535 characters
tinytext	variable-length string, up to 255 characters
text	variable-length string with a maximum of 65,535 characters
mediumtext	variable-length string, up to 2 24-square-1 Characters
longtext	variable-length strings, up to 2 of 32-1 characters

Both char and varchar:1 can limit the maximum number of characters stored by specifying N, char (20) and varchar (20) will store up to 20 characters, and more characters will be truncated. n must be less than the maximum number of characters allowed for this type. 2.char type specified n, if the number of characters deposited is less than N, the following will be filled with spaces, the query will be the end of the space to be removed, so char type stored by the end of the string can not have spaces, varchar is not subject to this restriction. 3. Internal storage mechanism is different. Char is a fixed length, char (4) Whether it is a character, 2 characters or 4 characters (in English), will occupy 4 bytes, varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), so varchar (4 ), depositing one character takes 2 bytes, 2 characters occupy 3 bytes, and 4 characters occupy 5 bytes. The string retrieval speed of the 4.char type is faster than the varchar type.  varchar and text:1 are variable-length and can store up to 65,535 characters. 2.varchar can specify that the N,text cannot be specified, the internal storage varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), the text is the actual number of characters + 2 bytes. The 3.text type cannot have a default value. 4.varchar can create indexes directly, text creates indexes to specify the number of characters before. The query speed varchar is faster than text, and the index does not seem to work in the case where the index is created, see this article: Http://forums.mysql.com/read.php?24,105964,105964 5. Binary data (Xxxblob) Xxxblob and xxxtext are corresponding, but stored in different ways, Xxxtext is stored as text, if the storage of English words are case-sensitive, and Xxxblob is stored in binary mode, is not case-sensitive. The data stored by Xxxblob can only be read out in its entirety. Xxxtext can specify a character set, Xxxblob does not specify a character set.  6. Date Time Type (Date,time,datetime,timestamp)

MySQL data type	Meaning
Date	Date ' 2008-12-2 '
Time	Time ' 12:25:36 '
Datetime	Date Time ' 2008-12-2 22:06:44 '
Timestamp	Not fixed

Timestamp is special, if you define a field of type timestamp, the time of this field is automatically refreshed when other fields are modified. So the field of this data type can hold the last time the record was modified, not the actual time of storage. Properties of the data type

mysql keyword	Meaning
Null	Data columns can contain null values
Not NULL	Data columns are not allowed to contain null values
DEFAULT xxx	Default value, if no value is specified when inserting the record, the default value will be taken
PRIMARY KEY	Primary key
Auto_increment	Increment, if no value is specified when inserting a record, add 1 to the value of the previous record and apply only to integer types
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set

MySQL data table type data type