MySQL longtext can't have a default value

user_7489218 0
Viewed 279

I know this is everywhere, but unfortunately I've tried everything and I can't seem to figure out why I can't add a default value to longtext type.

Any help would be appreciated.

My MySQL version: 5.7

mysql
2 Answers
ysth_173890

The DEFAULT clause should support any legal value for the data type of the respective column.

But LONGTEXT can be up to 4GiB. Therefore the CREATE TABLE or ALTER TABLE you use to define the column's default value must be at least that long.

But you can't make an SQL statement that is so long.

Re comment:

Consider this hypothetical table:

CREATE TABLE novel (
 title VARCHAR(80) DEFAULT 'Moby Dick',
 content LONGTEXT DEFAULT 'Call me Ishmael. Some years ago—never mind how long precisely—having little or no money in my purse, and nothing particular to interest me on shore, I thought I would sail about a little and see the watery part of the world. It is a way I have of driving off the spleen and regulating the circulation. Whenever I find myself growing grim about the mouth; whenever it is a damp, drizzly November in my soul; whenever I find myself involuntarily pausing before coffin warehouses, and bringing up the rear of every funeral I meet; and especially whenever my hypos get such an upper hand of me, that it requires a strong moral principle to prevent me from deliberately stepping into the street, and methodically knocking people’s hats off—then, I account it high time tozz get to sea as soon as I can. This is my substitute for pistol and ball. With a philosophical flourish Cato throws himself upon his sword; I quietly take to the ship. There is nothing surprising in this. If they but knew it, almost all men in their degree, some time or other, cherish very nearly the same feelings towards the ocean with me...
);

This is impractical to have such a long DEFAULT value, because a very long string makes the CREATE TABLE statement very long.

In fact, MySQL does not support it, because the table metadata such as the default value for each column is stored in a file with a .frm extension, and this table has a size limit of 64KB. See https://www.percona.com/blog/2013/04/08/understanding-the-maximum-number-of-columns-in-a-mysql-table/

MySQL 8.0 changed the implementation of metadata storage, and the .frm file was made obsolete. But there still must be a limit on the size of metadata.

Bill_Karwin_208600
Related