2009-05-19 Mutual 05-20 MySQL study notes 02 04/06 Update SLTechnology News&Howtos

2009-05-19 Mutual 05-20 MySQL study notes 02

2025-04-06 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

1. Integer type

TINYINT 1 byte

SMALLINT 2 byt

MEDIUMINT 3 byt

INT or INTEGER 4 bytes

BIGINT 8 byt

The number in parentheses after the integer type represents the display width of the integer field and is automatically filled with spaces if there are not enough digits, but this does not affect the size of the field and the range of values that can be stored.

The UNSIGNED modifier states that the field holds only positive values; the ZEROFILL modifier states that 0 is used instead of spaces to fill the output value, and fields that use it cannot store negative values.

[@ more@] mysql > create table int_test

-> (

-> num1 tinyint

-> num2 tinyint (3)

-> num3 tinyint unsigned

-> num4 tinyint (3) zerofill

->)

Query OK, 0 rows affected (0.12 sec)

Mysql > desc int_test

+-- +

+-- +

| | num1 | tinyint (4) | YES | | NULL |

| | num2 | tinyint (3) | YES | | NULL |

| | num3 | tinyint (3) unsigned | YES | | NULL |

| | num4 | tinyint (3) unsigned zerofill | YES | | NULL |

+-- +

4 rows in set (0.03 sec)

Mysql > insert into int_test values (1pm 1pm 1pm 1)

Query OK, 1 row affected (0.43 sec)

Mysql > insert into int_test values (- 1)

ERROR 1264 (22003): Out of range value for column 'num3' at row 1

Mysql > insert into int_test values (- 1)

ERROR 1264 (22003): Out of range value for column 'num4' at row 1

Mysql > insert into int_test values (127127127127)

Query OK, 1 row affected (.59 sec)

Mysql > insert into int_test values (128128128128)

ERROR 1264 (22003): Out of range value for column 'num1' at row 1

Mysql > insert into int_test values (127127128127)

Query OK, 1 row affected (0.04 sec)

Mysql > select * from int_test

+-+

| | num1 | num2 | num3 | num4 | |

+-+

| | 1 | 1 | 1 | 001 | |

| | 127 | 127 | 127 | 127 | |

| | 127 | 127 | 128 | 127 | |

+-+

3 rows in set (0.03 sec)

two。 Floating point type

FLOAT 4 byt

DOUBLE or REAL DOUBLE PRECISION 8 bytes

DECIMAL or DEC NUMERIC for DECIMAL (M > D), if M > D, it is masking 2, otherwise it is dumb2.

The two numbers in parentheses after the floating point type are a display width indicator and a decimal point indicator, respectively.

The UNSIGNED and ZEROFILL modifiers can also be used for floating-point types, and the effect is similar to that for integer types.

MySQL saves the DECIMAL data type in binary format and is used to hold values that must be precise.

Mysql > create table float_test

-> (

-> num1 float

-> num2 float (5pc2)

-> num3 float unsigned

-> num4 float (5pm 2) zerofill

-> num5 decimal

-> num6 decimal (5pc2)

->)

Query OK, 0 rows affected (0.58 sec)

Mysql > desc float_test

+-- +

+-- +

| | num1 | float | YES | | NULL |

| | num2 | float (5) | YES | | NULL | |

| | num3 | float unsigned | YES | | NULL |

| | num4 | float (5) unsigned zerofill | YES | | NULL | |

| | num5 | decimal (1010) | YES | | NULL | |

| | num6 | decimal (5) | YES | | NULL | |

+-- +

6 rows in set (0.05 sec)

Mysql > insert into float_test values (123.321, 123.321, 123.321, 123.321, 123.32

1, 123.321)

Query OK, 1 row affected, 2 warnings (0.04 sec)

Mysql > insert into float_test values (- 123.3,-123.3,-123.3,-123.3,-123.3,-1)

23.3)

ERROR 1264 (22003): Out of range value for column 'num3' at row 1

Mysql > insert into float_test values (- 123.3,-123.3, 123.3,-123.3,-123.3,-12)

3.3)

ERROR 1264 (22003): Out of range value for column 'num4' at row 1

Mysql > insert into float_test values (- 123.3,-123.3, 123.3, 123.3,-123.3,-123)

.3)

Query OK, 1 row affected, 1 warning (0.04 sec)

Mysql > insert into float_test values (123.456, 123.456, 123.456, 123.456, 123.45

6, 123.456)

Query OK, 1 row affected, 2 warnings (0.03 sec)

Mysql > insert into float_test values (- 123.456,-123.456, 123.456, 123.456,-123)

.456-123.456)

Query OK, 1 row affected, 2 warnings (0.03 sec)

Mysql > select * from float_test

+-+ +

| | num1 | num2 | num3 | num4 | num5 | num6 | |

+-+ +

| | 123.321 | 123.32 | 123.321 | 123.32 | 123.32 |

| |-123.3 |-123.30 | 123.3 | 123.30 |-123.30 | |

| | 123.456 | 123.46 | 123.456 | 123.46 | 123.46 |

| |-123.456 |-123.46 | 123.456 | 123.46 |-123.46 | |

+-+ +

4 rows in set (0.02 sec)

3. String type

CHAR 0-255byt

VARCHAR 0-255byt

TINYBLOB 0-255byt

TINYTEXT 0-255byt

BLOB 0-65535 byt

TEXT 0-65535 byt

MEDIUMBLOB 0-16777215 byt

MEDIUMTEXT 0-16777215 byt

LONGBLOB 0-294967295 byt

LONGTEXT 0-294967295 byt

The CHAR type is used for fixed-length strings, followed by a number in parentheses that specifies the length of the value to be stored, ranging from 0 to 255. Values smaller than the specified length are appropriately filled with spaces, and values larger than the specified length are automatically truncated.

The VARCHAR type is used for variable-length strings, followed by a number in parentheses that specifies the maximum length of the stored value (which must be specified), ranging from 0 to 255. Values smaller than the specified maximum length are stored at their actual size, are not filled with spaces, and values larger than the specified maximum length are automatically truncated.

Values of CHAR and VARCHAR types are case-insensitive by default, which can be changed using the BINARY modifier.

Mysql > create table char_test

-> (

-> string1 char

-> string2 char (5)

-> string3 char (5) binary

-> string4 varchar (5)

-> string5 varchar (5) binary

->)

Query OK, 0 rows affected (0.49 sec)

Mysql > desc char_test

+-+ +

+-+ +

+-+ +

5 rows in set (0.01 sec)

Mysql > insert into char_test values ('abc',' abc')

ERROR 1406 (22001): Data too long for column 'string1' at row 1

An error has occurred that the length of the first field of the record to be inserted exceeds the default length of 1 for the string1 field, because the server is running in strict mode. View the relevant settings in my.ini:

# Set the SQL mode to strict

Sql-mode= "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Modify this section to:

# Set the SQL mode to strict

# sql-mode= "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Sql-mode= "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restart the server and try again:

Mysql > insert into char_test values ('abc',' abc')

Query OK, 1 row affected, 1 warning (0.05sec)

This time the record was successfully inserted into the char_ test table. Continue the previous experiment:

Mysql > insert into char_test values ('abc',' abc')

Query OK, 1 row affected, 1 warning (0.05sec)

Mysql > insert into char_test values ('abcde',' abcde'

);

Query OK, 1 row affected, 1 warning (0.44 sec)

Mysql > select * from char_test

+-+

+-+

| | a | abc | abc | abc | abc | |

+-+

2 rows in set (0.00 sec)

Mysql > select * from char_test where string2 = 'ABC'

+-+

+-+

| | a | abc | abc | abc | abc | |

+-+

1 row in set (0.41 sec)

Mysql > select * from char_test where string3 = 'ABC'

Empty set (0.00 sec)

Mysql > select * from char_test where string4 = 'ABC'

+-+

+-+

| | a | abc | abc | abc | abc | |

+-+

1 row in set (0.00 sec)

Mysql > select * from char_test where string5 = 'ABC'

Empty set (0.00 sec)

The BLOB and TEXT (TEXT type is a case-insensitive BLOB type?) types are used to store long variable-length strings, but they cannot be assigned a maximum length as the VARCHAR type does. The maximum length of each BLOG or TEXT type is fixed, and the excess value is automatically truncated. If you are not sure which BLOB or TEXT type to use, you can add a parenthesis after BLOB or TEXT (otherwise not allowed) and enter the maximum length of the string in the parentheses, so that the system will automatically select the appropriate type.

Mysql > create table blob_text_test

-> (

-> string1 blob (255)

-> string2 text (255)

String3 blob (65535)

String4 text (65535)

String5 blob (16777215)

String6 text (16777215)

String7 blob (294967295)

String8 text (294967295)

->)

Query OK, 0 rows affected (0.52sec)

Mysql > desc blob_text_test

+-+ +

+-+ +

+-+ +

8 rows in set (0.03 sec)

4. Date and time type

Type size range format usage

DATE 3 1000-01-01 ~ 9999-12-31 YYYY-MM-DD date value

TIME 3'- 838 HH:MM:SS time value or duration

YEAR 1 1901 ~ 2155 YYYY year value

DATETIME 8 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS mixed date and time valu

TIMESTAMP 8 1970-01-01 00:00:00 to 2037 sometime YYYYMMDDHHMMSS mixed date and time, time stamp

These types can be described as strings or sequences of integers without delimiters, and if described as strings, they should follow the format in the range column of the table above.

Mysql > create table date_time_test

-> (

-> dt1 date

-> dt2 time

-> dt3 year

-> dt4 datetime

-> dt5 timestamp

->)

Query OK, 0 rows affected (0.49 sec)

Mysql > insert into date_time_test values ('2008-05-12,' 014, 28, 15, 57, 2008, 20)

08-05-12 14-14-28-57-7, '2008-05-12-14-14-28-57')

Query OK, 1 row affected (0.03 sec)

Mysql > insert into date_time_test values (20080512, 0142857, 2008, 2008051214285

720080512142857)

Query OK, 1 row affected (0.03 sec)

Mysql > select * from date_time_test

+-+

| | dt1 | dt2 | dt3 | dt4 | dt5 | |

+-+

| | 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |

+-+

2 rows in set (0.00 sec)

As you can see, the records inserted using string and integer sequence descriptions are exactly the same. When the values entered are not in a standard format, MySQL tries to "understand" them as much as possible:

Mysql > insert into date_time_test values ('2008-5-12,' 3-15-4-5-12),'08-5-12

3. 3, 4, 5, 5, 5, 5, 12, 3, 3, 4, 5, 5, 5, 12, 3, 3, 4, 4, 5, 5, 5, 5, 5, 12, 3, 3, 3, 3, 4, 4, 3, 5, 5, 5, 5, 12, 3, 3, 3, 4, 4, 3, 5, 5, 5, 12, 5, 5, 12, 3, 3, 3, 4, 5, 5, 5, 5, 12, 5, 5, 12, 3, 4, 5, 5, 5, 5, 12, 3, 4, 5, 5, 5, 5, 12, 5, 5, 12, 3, 3, 4, 5, 5')

Query OK, 1 row affected (0.04 sec)

Mysql > insert into date_time_test values (080512, 142857, 008, 2008512345, 08051

21428)

Query OK, 1 row affected, 1 warning (0.44 sec)

Mysql > insert into date_time_test values ('2008-05', 1428,'8','8-5-12 14 14-28'

'2008-05 1415 28')

Query OK, 1 row affected, 2 warnings (0.04 sec)

Mysql > select * from date_time_test

+-+

| | dt1 | dt2 | dt3 | dt4 | dt5 | |

+-+

| | 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |

| | 2008-05-12 | 03:04:05 | 2008 | 2008-05-12 03:04:05 | 2008-05-12 03:04:05 |

| | 2008-05-12 | 14:28:57 | 2008 | 0000-0000: 00:00 | 2000-08-05 12:14:28 |

| | 0000-00-00 | 00:14:28 | 2008 | 0008-05-12 14:28:00 | 0000-0000: 00:00 |

+-+

5 rows in set (0.00 sec)

For the TIMESTAMP type, when it is specified as NULL, or when it is not explicitly specified as the first field in the record, MySQL automatically populates it with the current date and time, while the DATETIME type does not.

Mysql > create table ts_dt_test

-> (

-> dt1 timestamp

-> dt2 timestamp

-> dt3 datetime

->)

Query OK, 0 rows affected (0.52sec)

Mysql > desc ts_dt_test

| |

TAMP |

| | dt2 | timestamp | NO | | 0000-0000: 00:00 |

| |

| | dt3 | datetime | YES | | NULL |

| |

3 rows in set (0.01sec)

Mysql > insert into ts_dt_test values (NULL, NULL, NULL)

Query OK, 1 row affected (0.03 sec)

Mysql > insert into ts_dt_test (dt3) values (NOW ())

Query OK, 1 row affected (0.06 sec)

Mysql > insert into ts_dt_test (dt2, dt3) values (NOW (), NOW ())

Query OK, 1 row affected (0.03 sec)

Mysql > select * from ts_dt_test

+-+

| | dt1 | dt2 | dt3 | |

+-+

| | 2009-05-20 15:09:20 | 2009-05-20 15:09:20 | NULL |

| | 2009-05-20 15:10:20 | 0000-0000: 00:00 | 2009-05-20 15:10:20 |

| | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 |

+-+

3 rows in set (0.00 sec)

Finally, try to insert illegal date and time values, and MySQL automatically sets them to 0.

Mysql > insert into ts_dt_test values ('2009-02-30,' 2009-05-32, '2009-01-01 24

: 00PUR 00')

Query OK, 1 row affected, 3 warnings (0.03 sec)

Mysql > select * from ts_dt_test

+-+

| | dt1 | dt2 | dt3 | |

+-+

| | 2009-05-20 15:09:20 | 2009-05-20 15:09:20 | NULL |

| | 2009-05-20 15:10:20 | 0000-0000: 00:00 | 2009-05-20 15:10:20 |

| | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 |

| | 2009-05-20 15:26:44 | 2009-05-20 15:20:11 | NULL |

| | 0000-0000: 00:00 | 0000-0000: 00:00 | 0000-0000: 00:00 |

+-+

5 rows in set (0.00 sec)

5. Compound type

MySQL has two composite types, ENUM and SET, which extend the SQL specification. The ENUM type must select a single value from a collection of allowed values, while the SET type can select any number of values from the set of allowed values. The detailed usage will not be repeated here.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.