How to solve the problem of returning longtext fields in MySql

Return of longtext field in MySql

Longtext fields have been used in recent development. The maximum length of this field in mysql is 4G

As shown in the figure below

One encountered during development The problem is. For example, there is an article table, and then our page needs to display the data to the front end in the form of a list (only displaying a few fields, such as author, title, etc., for example, placing it in a table to display multiple records), but the table All the information in the record is found, and then when the user clicks on a record, it will jump to the details page and display the detailed information.

This way, when the amount of data is relatively large, or when the content of the text is relatively large, problems will arise.

As the amount of data in the page increases, the page loading time will become longer until the data list is finally displayed. This will seriously affect the use effect.

Solution

Of course it is a problem with the sql statement. When querying the entire list like above, you can query other fields instead of the longtext field. Then when the user clicks on a certain piece of data, he will go to the database to check this piece of data based on the id of the piece of data. At this time, he can check out the longtext.

Speaking of which, I would like to talk about another situation, that is, sometimes the data found from the database is encapsulated into an entity class, but the value of a certain field cannot be obtained, which is null. The SQL query statement must be checked to ensure that the field in the returned result has been mapped and encapsulated to the corresponding class field.

Generally, this is because the field is not encapsulated in the result set.

Range of Text field in Mysql

text: Stores non-Unicode data of variable length, with a maximum length of 2^31-1 characters.

The text column cannot have a default value. During the storage or retrieval process, there is no case conversion. If you specify the length later, no error will be reported, but this length will not work, which means that you insert the data. When the length exceeds the length you specify, it can still be inserted normally.

In MySQL, the maximum length of a column of type TEXT is 65,535 characters (that is, 2 to the 16th power minus 1).

If you think the text length is not enough, you can choose

• MEDIUMTEXTThe maximum length is 16,777,215.

• LONGTEXTThe maximum length is 4,294,967,295

• ##Text is mainly used to store non-binary text Text, such as forum posts, questions, or questions and answers that Baidu knows.

What needs to be clarified is the difference between text and char varchar blob.

mysql Modify text field length_mysql text field length? The problem of insufficient text field length in mysql database...

type is a variable-length string, up to 65535 characters;

can change the field type to MEDIUMTEXT (maximum storage 16777215 characters) or LONGTEXT (up to 4294967295 characters).

MySQL supports 4 TEXT field types (TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT) and this post looks at the maximum length of each of these field types.

MyISAM tables in MySQL have a maximum size of a row of 65,535 bytes, so all the data in a row must fit within that limit. However, the TEXT types are stored outside the table itself and only contribute 9 to 12 bytes towards that limit. (For more information about this refer to the MySQL Manual - Data Storage Requirements chapter). TEXT data types are also able to store much more data than VARCHAR and CHAR text types so TEXT types are what you need to use when storing web page or similar content in a database. The maximum amount of data that can be stored in each data type is as follows: TINYTEXT256 bytes

TEXT65,535 bytes~64kb

MEDIUMTEXT 16,777,215 bytes~16MB

LONGTEXT4,294,967,295 bytes~4GB

In most circumstances the TEXT type is probably sufficient, but if you are coding a content management system it’s probably best to use the MEDIUMTEXT type for longer pages to ensure there are no issues with data size limits.

How many characters do Chinese characters occupy in utf8mb4

Answer: 3

The explanation of utf8mb4 in the official MySQL manual is

The default utf8 of existing database versions is utf8mb3, pay attention to the "same length" ".

So under utf8mb4, English takes up 1 byte, general Chinese characters take up 3 bytes, and emoji takes up 4 bytes.

Regarding the code insertion error into the emoji table, please check whether the table encoding and database connection encoding are both utf8mb4.

Conclusion: The database and tables are both set to utf8mb4.

The above is the detailed content of How to solve the problem of returning longtext fields in MySql. For more information, please follow other related articles on the PHP Chinese website!