[GO] Analyze the length of MySQL data type "MySQL data field in the role of length and decimals! Familiar with MySQL must see "

You see this article from HTTP://WWW.CNBLOGS.COM/AYANMW

Reprinted from: http://blog.csdn.net/daydreamingboy/article/details/6310907

Analyze the length of the MySQL data type

MySQL has several data types that can limit the "length" of a type, with char (length), VARCHAR (length), TINYINT (length), SMALLINT (length), mediumint (length), INT ( Length), BIGINT (length), FLOAT (length, decimals), DOUBLE (length, decimals), and decimal (length, decimals).

However, the length of these data types does not refer to the size of the data. Specifically, that is:
(1) Char, Varcahr length refers to the length of the character, for example, char[3] can only put the string "123", if inserting data "1234", then intercept from the high, and become "123". Varcahr.

(2) The lengths of TINYINT, SMALLINT, mediumint, int, and bigint are irrelevant to the size of the data! Length refers to the width of the display, for example:

Mysql> CREATE TABLE test (ID int (3) zerofill); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO test (ID) VALUES (1), (1234); Query OK, 2 rows affected (0.06 sec) records:2  duplicates:0  warnings:0mysql> SELECT * from test;+------+| id< c2/>|+------+|  001 | | 1234 |+------+2 rows in Set (0.00 sec)

As you can see, the display width of the ID is 3, the insufficient left side is 0, and the data length exceeds the original output. If there is no Zerofill, the display width is not visible and there is no leading zero.

(3) The length of FLOAT, double, and decimal refers to all digits (including after the decimal point), such as decimal (4,1), which refers to the total number of digits is 4, 1 digits after the decimal point, and if 1234 is inserted, the queried data is 999.9. The process is as follows

Mysql> ALTER TABLE test add Realnum decimal (4,1); Query OK, 2 rows affected (0.03 sec) records:2  duplicates:0  warnings:0mysql> insert INTO Test (Id,realnum) Val UEs (2,1234); Query OK, 1 row affected, 1 warning (0.05 sec) mysql> select * from test;+------+---------+| ID   | realnum |+------+---------+|  001 |    NULL | | 1234 |    NULL | |  002 |   999.9 |+------+---------+3 rows in Set (0.02 sec)

Appendix Common MySQL data types (left as Memo)

Type	Size	Description
Cahr (Length)	Length byte	Fixed-length field, 0~255 characters
VARCHAR (Length)	String length + 1 bytes or string length + 2 bytes	Variable Length field, 0~65 535 characters long
Tinytext	String length + 1 bytes	string with a maximum length of 255 characters
TEXT	String length + 2 bytes	string with a maximum length of 65 535 characters
Mediumint	String length + 3 bytes	string with a maximum length of 16 777 215 characters
Longtext	String length + 4 bytes	string with a maximum length of 4 294 967 295 characters
TINYINT (Length)	1 bytes	Range: -128~127, or 0~255 (unsigned)
SMALLINT (Length)	2 bytes	Range: 32 768~32 767, or 0~65 535 (unsigned)
Mediumint (Length)	3 bytes	Range:-8 388 608~8 388 607, or 0~16 777 215 (unsigned)
INT (Length)	4 bytes	Range:-2 147 483 648~2 147 483 647, or 0~4 294 967 295 (unsigned)
BIGINT (Length)	8 bytes	Range:-9 223 372 036 854 775 808~9 223 372 036 854 775 807, or 0~18 446 744 073 709 551 615 (unsigned)
FLOAT (Length, decimals)	4 bytes	A smaller number with a floating decimal point
DOUBLE (Length, decimals)	8 bytes	A larger number with a floating decimal point
DECIMAL (Length, decimals)	length+1 bytes or length+2 bytes	A double that is stored as a string that allows a fixed decimal point
DATE	3 bytes	Use YYYY-MM-DD format
Datetime	8 bytes	Using YYYY-MM-DD HH:MM:SS format
TIMESTAMP	4 bytes	in YYYYMMDDHHMMSS format; acceptable range terminated in 2037
Time	3 bytes	Use HH:MM:SS format
Enum	1 or 2 bytes	Shorthand for enumeration (enumeration), which means that each column can have one of several possible values
SET	1, 2, 3, 4, or 8 bytes	Like an enum, except that each column can have multiple possible values

Reference: Http://blog.csdn.net/reanimation1/archive/2008/08/26/2833327.aspx

MySQL view table structure of the MySQL statement is:

MySQL view table structure command, as follows: Desc table name; Show columns from table name; Describe table name; Show create table table name; use Information_schema;select * from columns Where table_name= ' table name '; By the way note: show databases; --Display Database list use database name;         --Set as current working database show tables;         --Displays the list of tables under the current working database a unique index ak_pas_name (pac_name) in table Tb_webparamcounter, execute the following SQL Modify index ALTER TABLE Tb_webparamcounter Drop Index Ak_pas_name;alter Table tb_webparamcounter add UNIQUE ak_pas_name (pc_id,pac_name); If you find that the logic of the index is incorrect, you need to add a field to it. Execute ALTER TABLE Tb_webparamcounter DROP INDEX ak_pas_name;alter table tb_webparamcounter add UNIQUE ak_pas_name (pc_id,pac_ Name,pac_value); Note: At this point, the Pc_id,pac_name,pac_value three fields are not FOREIGN key, otherwise you must drop FOREIGN key first, and then redo the previous step by the way Oracleselect * From V$database;select * from All_users;select * from User_tables;

Reprint please indicate the source: HTTP://WWW.CNBLOGS.COM/AYANMW I will be very happy!

[to] parse the length of the MySQL data type "MySQL data field in the role of length and decimals! Familiar with MySQL must see "