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

The CHAR type is of fixed length and ranges in length from 0 to 255. If the stored value does not overwrite the field length, the rest of the stored data automatically adds spaces to the right of the value.

The value in the VARCHAR field is a variable-length string ranging in length from 0 to 65535. The value of the VARCHAR field is stored as a 1-byte or 2-byte length prefix with data. The length prefix indicates the number of bytes stored in the field, using one length prefix if the value stored in a field does not exceed 255 bytes, and two length prefixes if the value stored in the field exceeds 255 bytes.

If strict SQL mode is not enabled, a value assigned to a CHAR or VARCHAR field that exceeds the maximum length of the field is truncated and saved to the database with a warning. For VARCHAR fields, regardless of the SQL mode used, the value assigned to the VARCHAR field that exceeds the maximum length of the field is truncated before the value is inserted, and a warning is generated. For CHAR fields, regardless of the SQL schema used, the part that exceeds the maximum length of the field is truncated and implicitly inserted into the database.

-- Test ①

MariaDB [test] > show variables like'% sql%mode%'

+-+

| | Variable_name | Value |

+-+

| | sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-+

1 row in set (0.26 sec)

MariaDB [test] > drop table vc

Query OK, 0 rows affected (0.04 sec)

MariaDB [test] > CREATE TABLE vc (v VARCHAR (4), c CHAR (4))

Query OK, 0 rows affected (.54 sec)

MariaDB [test] > INSERT INTO vc VALUES ('ab','ab')

Query OK, 1 row affected (0.14 sec)

-check to see if spaces are left in the inserted data

MariaDB [test] > SELECT CONCAT ('(', v,')'), CONCAT ('(', c,')) FROM vc

+-+ +

| | CONCAT ('(', v,')') | CONCAT ('(', c,')') |

+-+ +

| | (ab) | (ab) |

+-+ +

1 row in set (0.00 sec)

MariaDB [test] > select length (v), length (c) from vc

+-+ +

| | length (v) | length (c) | |

+-+ +

| | 4 | 2 |

+-+ +

1 row in set (0.00 sec)

-- insert data that exceeds the maximum value of the field into the table

MariaDB [test] > insert into vc values ('abcdefg','abcedeg')

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

MariaDB [test] > show warnings

+-+

| | Level | Code | Message | |

+-+

| | Warning | 1265 | Data truncated for column 'v' at row 1 | |

| | Warning | 1265 | Data truncated for column 'c' at row 1 | |

+-+

2 rows in set (0.00 sec)

MariaDB [test] > select * from vc

+-+ +

| | v | c | |

+-+ +

| | ab | ab |

| | abcd | abce |

+-+ +

2 rows in set (0.00 sec)

-- Test ②

MariaDB [test] > CREATE TABLE names (myname CHAR (10))

Query OK, 0 rows affected (0.13 sec)

MariaDB [test] > INSERT INTO names VALUES ('Monty')

Query OK, 1 row affected (0.00 sec)

-- View the execution of the data in the table with spaces in the condition in which the = is used in the WHERE condition

MariaDB [test] > SELECT myname = 'Monty', myname =' Monty' FROM names

+-+ +

| | myname = 'Monty' | myname =' Monty' | |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > select length (myname) from names

+-+

| | length (myname) |

+-+

| | 5 |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname = 'Monty'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname = 'Monty'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname = 'Monty'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname = 'Monty'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname = 'Monty'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname = 'Monty'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname = 'Monty'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname = 'Monty'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

-- View the execution of the data in the table under like conditions

MariaDB [test] > SELECT myname LIKE 'Monty', myname LIKE' Monty' FROM names

+-+

| | myname LIKE 'Monty' | myname LIKE' Monty' | |

+-+

| | 1 | 0 |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname like'% Monty%'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname like 'Monty%'

+-+

| | myname |

+-+

| | Monty |

+-+

1 row in set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname like 'Monty%'

Empty set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname like 'Monty%'

Empty set (0.00 sec)

MariaDB [test] > SELECT * FROM names where myname like 'Monty%'

Empty set (0.00 sec)

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.