mysqlsqllongtext

SQL max size LONGTEXT

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?

Solution

  • 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 (224 − 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.