SQL max size LONGTEXT

Question

I created this table:

CREATE TABLE Hospital_MedicalRecord(
  recNo CHAR(5),
  patient CHAR(9),
  doctor CHAR(9),
  enteredOn DATETIME NOT NULL,
  diagnosis LONGTEXT NOT NULL,
  treatment TEXT(1000),
  PRIMARY KEY (recNo, patient),
  CONSTRAINT FK_patient FOREIGN KEY (patient) REFERENCES Hospital_Patient(NINumber),
  CONSTRAINT FK_doctor FOREIGN KEY (doctor) REFERENCES Hospital_Doctor(NINumber)
  ON DELETE CASCADE
);

How can one make diagnosis contain some long text but never more than 2^24 bytes? I've looked into LONGTEXT but I couldn't find a way to limit it since it can go up to 2^34 I believe?

You can add a trigger if you really feel compelled to distinguish between those two sizes. In some very recent version of MySQL, you could also use a `check` constraint. — Gordon Linoff, Dec 10 '19 at 22:46

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Use MEDIUMTEXT.

https://dev.mysql.com/doc/refman/8.0/en/string-type-overview.html

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 16,777,215 (2²⁴ − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value.

The wording is a little strange. The length limit is really on bytes, not characters.

score 1 · Answer 2 · answered Dec 10 '19 at 23:00

LONGTEXT is limited to the maximum that the filesystem permits for a 32 bit system it is limited to 2^32 that is approximately 4.000.000.000 characters (if you do not use multi-byte characters), I have not calculated exactly.
Then you have MEDIUMTEXT with 2^24 characters .. around 16.000.000 characters. TEXT has a limit at 2^16 character that is much smaller, about 64.000 characters (if you do not have multibyte). What You need is called MEDIUMTEXT

SQL max size LONGTEXT

2 Answers2