MySQL Learning note (a) data type

Learning content:

MySQL basic data type.

1. Number type:

I. Integral type

MySQL data type	Meaning (Signed)
tinyint (m)	One byte range ( -128~127)
smallint (m)	Two byte range ( -32768~32767)
Mediumint (M)	Three byte range ( -8388608~8388607)
Int (m)	Four byte range ( -2147483648~2147483647)
BigInt (M)	Eight byte range (18 +-9.22*10 of the X-square)

If unsigned is added to the value range, the maximum value is doubled, such as the tinyint unsigned value range (0~256).
m in int (m) is the display width in the result set of the select query, does not affect the actual range of values, does not affect the width of the display, do not know what the use of this m.

Ii. floating-point

MySQL data type	Meaning
Float (m,d)	Single-precision floating-point 8-bit precision (4 bytes) m total number, D decimal place
Double (m,d)	Double-precision floating-point 16-bit precision (8 bytes) m total number, D decimal place

Set a field defined as float (5,3), if you insert a number 123.45678, the actual database is 123.457, but the total number is actually the same, that is, 6 bits.

Iii. fixed-point number

Floating-point types hold approximate values in the database, while the fixed-point type stores the exact values in the database.
The decimal (m,d) parameter m<65 is the total number, d<30 and d<m is the decimal place.

Iv. string (char,varchar,_text)

MySQL data type	Meaning
CHAR (n)	Fixed length, up to 255 bytes
VARCHAR (n)	Fixed length, up to 65535 bytes
Tinytext	Variable length, up to 255 bytes
Text	Variable length, up to 65535 bytes
Mediumtext	Variable length, up to 2 of 24 square-1 characters
Longtext	Variable length, up to 2 of 32 square-1 characters

Char and varchar:
1.char (n) If the number of characters in the deposit is less than n, then the space is appended to it, and then the space is removed when queried. So the char type stores a string with no spaces at the end, and varchar is not limited to this.
2.char (n) fixed length, char (4) regardless of whether it is deposited in several characters, 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), A deposit of 3 characters will take up 4 bytes.
The string retrieval speed of the 3.char type is faster than the varchar type.

varchar and text:
1.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 2.text type cannot have a default value.
3.varchar can create indexes directly, text creates indexes to specify the number of characters before. varchar queries are faster than text, and the index of text does not seem to work in the case of all indexes being created.

V. Binary data

1._blob and _text are stored in different ways, _text stored in text, English storage is case-sensitive, and _blob is stored in binary mode, regardless of case.
The data stored by 2._blob can only be read out in its entirety.
3._text can specify a character set, _blo does not specify a character set.

2. Date and Time type

MySQL data type	Meaning
Date	Date ' 2015-04-23 ' contains only dates and does not contain time information
Time	Time ' 19:41:30 ' contains only the time, not including the date
Datetime	Date + time ' 2015-04-23 19:41:30 '
Timestamp	Automatic storage of modified time information

If you define a field that is timestamp, the time data in the fields is automatically refreshed when the other fields are modified, so the field of this data type can hold the last time the record was modified.

MySQL's date and time type, when the user enters an illegal time, the system will default to initialize to 0 ... For example:

' 19970523091528 ' and ' 970523091528 ' are interpreted as ' 1997-05-23 09:15:28 ', but ' 971122129015 ' is illegal (it has a meaningless minute part) that will become ' 0000-00-00 00:00:00 '. and allow the non-strict syntax to appear, for example: ' 98-12-31 11:30:45 ', ' 98.12.31 11+30+45 ', ' 98/12/31 11*30*45 ' and ' [email protected]@31 11^30^45 ' are equivalent.

Timestamp properties since MySQL 4.1

Note: In older versions of MySQL (before 4.1), the properties of the timestamp column type are significantly different in many ways than described in this section. If you need to convert your old timestamp data to work in MySQL 5.1, please refer to MySQL 4.1 reference manual for details.

The timestamp column is displayed in the same format as the datetime column. In other words, the display width is fixed at 19 characters, and the format is Yyyy-mm-dd HH:MM:SS.

The MySQL server can also run in MaxDB mode. When the server is running in this mode, the timestamp is equal to DateTime. That is, if the server is running in MAXDB mode when the table is created, the timestamp column is created as a datetime column. The result is that the column uses the datetime display format, has the same range of values, and does not automatically initialize or update the current date and time.

To enable MaxDB mode, use the--SQL-MODE=MAXDB server option when starting the server or set the SQL Server mode to MAXDB at run time by setting the global Sql_mode variable:

mysql> SET GLOBAL sql_mode=maxdb;

The client can use the following method to have the server run in MaxDB mode for its connection:

mysql> SET SESSION Sql_mode=maxdb

The following discussion is a table timestamp column that does not apply to MaxDB mode ...

In the default and on update words, you can use Current_timestamp or now (), or current_timestamp () ... The purpose is to initiate Automatic Updates ...
For example:

Create TableTime (Ts1timestamp default 0,//when you implement Automatic updates for a column instead of the first column for Automatic Updates, you must use default to give an initial value to disable initialization and Automatic Updates for the first column TS2timestamp default Current_timestamp      on Update Current_timestamp //the default and on update modifiers are also used ts2//Rst on Update Current_timestamp     //timestamp defaultCurrent_timestamp The order of the two is arbitrary and will not have an effect);

Current_timestamp indicates that the current timestamp is used, on update indicates automatic update ... The above scenario is not allowed to use the null attribute

Use the Null property:

 create  table   time (ts1  timestamp  null  default  null  , ts2  timestamp  null  default  current_timestamp,   Ts3  timestamp  null  default  0   

If you do not specify a null property, setting the column to a null setting sets it to the current timestamp. Note that the TIMESTAMP column that allows null values does not take the current timestamp unless either its default value is defined as Current_timestamp, or now () or current_timestamp is inserted into the column. In other words, a timestamp column that is defined as NULL is automatically updated only if it is created with the following definition:

CREATE TABLET (TSNULL DEFAULT Current_timestamp); otherwise-that is, if you use NULL instead of the default timestamp to define the timestamp column, as follows ...CREATE TABLET1 (TSNULL DEFAULT NULL);CREATE TABLET2 (TSNULL DEFAULT '0000-00-00 00:00:00');... You must explicitly insert a value that corresponds to the current date and time. For example:INSERT  intoT1VALUES(now ());INSERT  intoT2VALUES(Current_timestamp);

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	Default value
PRIMARY KEY	Primary key
Auto_increment	Auto increment, applies to integers
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set

Under Windows, the MySQL database keyword is case-insensitive ... null<==>null<==>null

Finally recommend a visual database of a software navicat, the database visualization, in which you can directly create a database and manipulate the database ...

MySQL Learning note (a) data type