MySQL data type

In general, a page can hold as many rows as possible, so the performance of the database is better. And choosing the wrong data type will increase the cost of later database maintenance.

The unsigned property is the unsigned number type.

Help int;

Select F1-f2 from t1;--MySQL to unsigned number of operations, return is unsigned, unless adjust Sql_mode settings

Try not to unsigned, unless zerofill, if int cannot save, use bigint

-- ---------------------------------------------

Zerofill-- Once the Zerofill attribute is enabled, MySQL automatically adds unsigned properties to the column.

Zerofill is just formatted output at query time (if the width is less than the set width, it is automatically populated with 0), the database is stored internally or 1 instead of 0001

Select A,hex (a) from t\g;

Int (11) without Zerofill, this 11 has no effect, int shaping is 4 bytes

Alter TALBE t1 modify F1 int (4) Zerofill;

-- ---------------------------------------------

SQL mode

SQL mode strict mode for MySQL provides a good data validation function, to ensure the accuracy of the data, try not to default (empty ).

Show variables like '%sql_mode% '; --Sql_mode has global and session sessions

SELECT @ @sql_mode;

Set sql_mode = '; --Sql_mode can set a lot of options, generally best set to strict mode

Set sql_mode = ' traditional ';

-- ---------------------------------------------

Default default is NULL, which is used when no value is explicitly assigned to a field

Not NULL The value cannot be null

-- ---------------------------------------------

Auto_increment

At most one in a table, no or no

Must be index (key), General primary key index

Select last_insert_id (); --Take the value of the auto-grow column

Reset:

ALTER TABLE user Auto_increment = 1;

--There are other parameters to set the step size and the initial value

-- ---------------------------------------------

Integral type: tinyint 1 B, smallint 2 B, mediumint 3 B, int 4 B, bigint 8 B.

Bit type: bit

Classical SQL Programming questions about numbers: 1, digital auxiliary table, 2, continuous range problem.

-- ---------------------------------------------

High Precision Type:

Decimal and numeric are basically the same, and the maximum number of digits is 65. can be applied to save money.

Salary Dec (5, 1)

Up to 5 digits, including 1 decimal places, integer digits up to 4 bits, 5 for precision, 1 for scale, 4 for 5

-- ---------------------------------------------

Float--Express decimals

where score = 3.14; --there is a problem

Float (6, 2)

Double precision

-- ---------------------------------------------

String type

Internationalized companies, the characters that need to be stored are diverse, so use Unicode-encoded character sets such as UTF8

Unicode is a way of encoding

Mysql>show CharSet;

UTF8 is a character set, which is a storage format

UTF is Unicode translation format, which turns Unicode encoded characters into a format to store

In the UTF8 character set, a Chinese character accounts for 2 ~ 4 bytes, most of which account for 3 bytes and a very small number of 2 or 4 bytes

gb2312 characters accounted for 2 bytes, and GBK was a superset of gb2312.

Generally for multinational business, the original character set is best to adopt UTF8 to solve the porting development compatibility problem.

-- ---------------------------------------------

Char

Show variables like '%char% ';

CHAR (8) Save ' AB ', account for 8 B, insert ' AB ', save 6 spaces after AB, select Remove space

8 characters in English or Chinese characters

-- ---------------------------------------------

varchar (8) is ' AB ', accounting for 3B--storage requires a prefix length list plus the actual stored characters, accounting for bytes.

8 characters in English or Chinese characters

Personal profile, microblogging content, the amount of text is not particularly large can be used varchar instead of text, some storage engine will be large varchar type string automatically converted to text or blob type.

Select Length (' Chinese ');

The varchar type is generally used because the char type length is fixed.

-- ---------------------------------------------

Binary and varbinary store binary strings. Binary (n), such as N, represents the number of bytes, while char (n) and so on represent the character length, while char and varchar characters compare the characters stored by themselves, ignoring the padding characters after the characters, and for binary and varbinary, the binary values are compared. Finally, for binary strings, the padding character is 0x00,char is the 0x20,0x00 comparison is the minimum character.

-- ---------------------------------------------

Text, blob, cannot have default value, specify prefix length when creating index, and use only first max_sort_length bytes of column when sorting

Text stored in a large number of text, such as news content, not case-sensitive

Blob (large binary object), case-sensitive

-- ---------------------------------------------

Enum (enumeration) and set types are collection types, but enum can enumerate up to 65,535 elements for the user table

, only one member can be selected at a time, and the set type enumerates up to 64 elements. Enum combined with Sql_mode Strict mode constraints, useful!

Sex men and women, permissions have no, whether administrator, whether hot news,

The enum type can only be selected from among the members, and the set type may select more than one,

Enum Usage: If you choose one of the multiple values, you can choose the enum type, for example, gender (male and female) two select one

Set type usage: Like personal hobbies, you can choose more than one, then this uses the set type we use

Sex enum (' M ', ' F ')

-- ---------------------------------------------

DateTime type: (MySQL is very easy to set the date and time)

DateTime 8 B, DATE 3 B, timestamp 4 B, year 1 B, Time 3B

Value with ' ' Cause

DateTime and timestamp display the same number of bytes, and the actual stored content datetime can be up to the current time in milliseconds, and the timestamp type can set a default value when building a table and DateTime does not (must be the table operation, that is, there are changes), and timestamp will automatically update the current time.

a DateTime type represents a larger time range than the timestamp type, so it is more appropriate to have a time range than a large selection of datetime types .

The time of the timestamp type is selected according to the time zone, and the timestamp type is selected if it is necessary to display the times in which the timezone corresponds .

MySQL 5.6. After version 4 supports fractional seconds: type_name (FSP). MySQL type is imprecise to subtle but can be extracted with the microsecond function

Select Now ();

Curdate () Date (now ());

Curtime () Time (now ());

Now, Current_timestamp, and sysdate are used to return the current system time, but the first two return the time when the SQL statement was executed, and the Sysdate function returns the time it takes to execute the current function.

Time-plus-decrement functions have date_add and date_sub. The Date_format function (to prevent misuse) is to format the print date according to user requirements.

Date Classic SQL Programming issues: 1, birthday issues, 2, overlapping issues, 3, days of the week.

The return value of the weekday function is 0~6,0 for the Monday,dayofweek function, which is 1~7,1 represents Sunday.

-- ----------------------------------------------------------------

Character

Characters (character) are the smallest of the semantic symbols in human language, such as ' A ', ' B ', etc.

Given a series of characters, assign a numeric value to each character

A numeric value that represents the corresponding character, which is the encoding of the character (encoding)

For example, we give the character ' a ' a value of 0, giving the character ' B ' a value of 1

Then 0 is the encoding of the character ' A ', and 1 is the encoding of the character ' B '.

Given a series of characters and given the corresponding numeric encoding

The set of all these characters and encoding pairs is the character set (character set)

For example, when a given character set is {' A ', ' B '}, {' A ': 0, ' B ': 1} is a character set

OS has character set, software has character set, if software has no character set then use OS character set

Proofing Rules (collation), also called Word Fu She, refers to the comparison rule between characters within the same character set

After you determine the character Fu She, you can define what is equivalent on a character set and the size relationship between characters

Case sensitivity is a sequence, case insensitive is a sequence

A character set can correspond to multiple characters Fu She, one of which is the default word Fu She

Each character Fu She uniquely corresponds to a set of characters

_ci is not case-sensitive-the application has this requirement, but a unique index will make an error

Collation: _cs Case-sensitive

_bin Comparison by coded values

For internationalized companies, there are a variety of characters that need to be stored, so use Unicode-encoded character sets such as UTF8

Unicode is a way of encoding, and UTF8 is a way of storing

UTF is the Unicode translation format, that is, the meaning of turning Unicode into a format

View all the available character sets

Show CharSet;

int, decimal cannot refer to character set

char, varchar, text string data types can only refer to the

Proofing rules:--No proofing rules are specified, the default proofing rules for that character set are used

Show collation;

Character set and proofing rules have 4 levels of settings

Server

Db

Table

Column

[Mysqld]

Character_set_server=utf8-mysql5.5 began to remove the Default-character-set parameter and replaced it with Default-character-server.

==

Create DATABASE DS CHARSET=GBK;

Alter DATABASE DS Charset=utf8; --Valid only for newly created tables

ALTER TABLE EMP charset = UTF8;

Client Character Set:

Default-character-set=utf8; #相当于set names UTF8; --set names can change the character set of the current session connection, the character set of the client for the current session, and the character set of the current session return set,mysql>status; Can see!

[MySQL]

Before data is written, set the client character set, that is, set names UTF8 before viewing the data;

This article is from the "8430296" blog, please be sure to keep this source http://8440296.blog.51cto.com/8430296/1584573

MySQL data type