3. mysql data type

1. Numeric type

Int ( TINYINT, SMALLINT, Mediumint,int,bigint )

The default is: signed

unsigned unsigned, cannot take negative numbers

The zerofill modifier Specifies a value of 0 (not a space) that can be used to really complement the output. Use this modifier to prevent the MySQL database from storing negative values

Zerofill is only formatted output at query time, the database is stored internally or 1 instead of 0001

Not NULL with default:

NOT NULL The value cannot be null, that is, write null in, the result is wrong, the judgment is used is

Default Defaults

This default value is used when a field is not explicitly assigned, and defaults to NULL

auto_increment automatic growth, usually only for the integer type, a table at most one, and generally the primary key index (key), unique

select last_insert_id ();      -- take the value of the autogrow column

Decimal , Numeric

decimal (m,d) can be abbreviated to Dec

to save money, salry Dec (5,2), up to a total 5 bit, which must 2 decimal places, integer digits up to 3 bits, 5 for precision, and 2 for scale. will be 4 5 in

Float

float single-precision, 4 B

Real Double Double precision are all Double , 8B

Note: with float It is better to specify the precision, or the query will error

The unsigned and Zerofill modifiers can also be used by the FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as the INT data type.

2. String Type

MySQL provides 8 basic string types that can store ranges from simple one character to huge chunks of text or binary string data.

varchar (m) when CharSet is UTF8, M is evaluated as [0,???]       

Char (8) Deposit ' AB ', 8 b varchar (8) is ' AB ', accounting for 3B

VARCHAR (8)--8 characters in English or Chinese characters

Personal profile, micro-blog content with varchar, the amount of text is not particularly large can be used varchar without text

Select Length (' Chinese ');

text, Blod,enum,set ( There are no default values )

• Text saved a lot of text, such as news content, not case-sensitive, search speed is slightly slower
• Blob (large binary object), case-sensitive

• enum only one member can be selected at a time

• Set similar to enum, but multiple members can be selected at one time

3. Date and Time type

DateTime 8B

Date 4 B

Time 4 B

Year 1B

Timestamp 4 b

Select Now ();

Select Curdate ();

Select Curtime ();

Timestamp related to time zones

when inserting: first turn into local time zone and store When removed: Displays after first turning to the local time zone

From for notes (Wiz)

3. mysql data type