Correct usage of MySQL auto-incrementing primary key and TEXT Data Types

This article mainly describes the correct use of the MySQL auto-incrementing primary key and TEXT data types. Sometimes we will use the MySQL database and also use the auto-incrementing primary key and TEXT data types. To prevent forgetting, first record the TEXT data type length:

 
 

  
  
TINYTEXT: 256 bytes   
  
  
TEXT: 65,535 bytes => ~64kb   
  
  
MEDIUMTEXT: 16,777,215 bytes => ~16MB   
  
  
BIGTEXT: 4,294,967,295 bytes => ~4GB  
 
 

For the table I want to create, the primary key uses auto-incrementing numbers. If a data column uses the TEXT data type to store TEXT, you can write the following SQL statement:

 
 

  
  
CREATE TABLE Corpus  
  
  
(  
  
  
id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,  
  
  
data TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
  
  
PRIMARY KEY (id) 
 
 

) Explain two concepts first:

Character Set is a set of symbols and encoding. Character set x indicates that the character set is SET to X.

A collation is a set of rules used to compare characters in a character set. There is a naming convention for proofreading rules: they start with their relevant Character Set names, usually include a language name, and are case-insensitive to _ ci), _ cs case-sensitive), or _ bin binary) end.

COLLATE utf8_general_ci indicates that the case-insensitive collation of utf8_general seems to be the default collation of the utf8 character set. Use "show collation like 'utf8% ';" to view details ). The table name is Corpus. The primary key is id. The MEDIUMINT data type can be used to indicate the approximate values of integers ranging from 1 to 999999, which is not accurate and easy to remember ~); UNSIGNED indicates the UNSIGNED type; AUTO_INCREMENT indicates the auto increment. The data column uses the TEXT data type and sets the character set of the column to utf8. The proofreading rule is utf8_general and case insensitive.

 

The above content describes the use of the MySQL auto-incrementing primary key and TEXT data types. I hope it will help you in this regard.