MySQL discrete notes-Data Type

Today, we are preparing to design a complete database. So we Google the MySQL database design principles, mainly data types and table design, for fear that we may forget to sort them out.

ReferenceArticle: MySQL field type Author: simaopig

Type	Description
Int	A value type. The value range is-2147483648 ~ 2147483647 unsigned 0 ~ 4294967295 up to 10,Therefore, you cannot save your mobile phone number.
Decimal	A value type that supports floating point numbers or decimal places.
Double	A value type that supports double-precision floating point numbers.
Date	Date Field in yyyymmdd format
Time	Hh: mm: time field in SS format
Datetime	Yymmdd hh: mm: date/time type in SS format.Note the space between "year, month, day" and "hour, minute, second"
Year	In yyyy or YY format, the range is 1901 ~ Do not ask me about the year field specified Between 2155 and 2155. I guess I will not be able to live that year.
Timestamp	Timestamp in yyyymmddhhmmss format
Char	String type with a maximum length of 255 characters and a fixed length
Varchar	String type with a maximum length of 255 characters but extended length
Text	String type with a maximum length of 65535 characters
Blob	Binary type of variable data
Enum	Data types that can accept values from the list of defined values
Set	The data type of zero or multiple values can be accepted from the set of defined values.

ExamplesCode:

 
Create Table data (ID tinyint );
 
 
Insert into data values (123456789 );
 
 
Select ID from data;
 
 
Because the storage range of tinyint is (-128,127), the query result is 127.
 
Create Table data (speed float (3, 1 ));
 
 
Insert into data values (123.765 );
 
 
Select speed from data
 
 
The query result is 123.8, Which is the result rounded up.
 
 
By default, MySQL is case insensitive. For example:
 
 
Create Table data (name char (5 ));
 
Insert into data values ('Hugo');
 
 
Select * from data where name ='Hugo'
 
 
Hugo will be searched.
 
 
Binary keyword, which tells MySQL that the subsequent string should be processed in binary mode. When the comparison operator is executed on the string, MySQL will keep the string case in mind. Both char and varchar use this modifier.
 
 
Alter table Data Change char (5) binary;
 
Select * from data where name ='Hugo'
 
 
That's all! Hugo cannot be found.
 
 
"The difference between the char type and vachar is that MySQL processes this indicator (length indicator) differently: Char treats this size as the exact size of the value (fill in the shorter value with spaces, so it reaches this size), and the varchar type regards it as the maximum value and only uses the number of bytes actually needed in the storage string (add an additional byte record length )"
 
 
"The text and blob types are different in classification and comparison. The Blob types are case-sensitive and the text types are case-insensitive. MySQL manual uses "text is a case-insensitive blob type" to describe this most accurately ."
 
 
Create Table data (birthday date );
 
 
Insert into data values ('2009-07 7'), (20090407 );
 
Select birthday from data;
 
 
The results are all "". By default, MySQL uses '-' to separate dates and uses ':' To separate time.
 
 
Drop table data;
 
 
Create Table data (Showtime time );
 
 
Insert into data values ('12:30:56'),('12: 30'), (123056 );
 
Select Showtime from data;
 
 
The query result is "12:30:56", "12:30:00", "12:30:56 ″.
 
 
MySQL also implements the maximum translation of this type for the values of two numbers in the year of the date, or for the two numbers in the input field of the year type. Because all year-type values must be stored in four numbers, MySQL tries to convert the year values of two numbers to the values of four numbers based on the value range: set ~ Values in the range of 69 are converted to 2000 ~ In the range of 2069 ~ The value in the range of 99 is converted to 1970 ~ Within 1979.
 
 
 
 
Problem:
 
 
How to design data types? Is it better for the user ID to use the numeric or numeric type?
 
 
Some searched suggestions:
 
 
Text fields of the ID type, such as the customer ID or order number, should be set to be larger than expected, because if the time is not long, you will be embarrassed to add additional characters. For example, assume that your customer ID is 10 digits long. Then you should set the field length of the database table to 12 or 13 characters. Is this a waste of space? There is one thing, but it is not as much as you think: a field is extended by three characters and there are 1 million records, with a small Index added, the whole database will not occupy 3 MB of space. However, this extra space can increase the database size without restructuring the entire database in the future.