0
TinyText - 255 chars
MediumText - 65535 chars

If I give 

ALTER TABLE attributes MODIFY stringValue text(32766)

It doesn't not work for me. It takes only the default max text size

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
shA.t
  • 16,580
  • 5
  • 54
  • 111
Manoj
  • 1,833
  • 3
  • 14
  • 11
  • If you are handcuff by alter then why don't you just reject it – Drew Jun 30 '15 at 05:05
  • How is it any different than an int and you don't want a number greater than 181 ? – Drew Jun 30 '15 at 05:07
  • i used "ALTER TABLE attributes MODIFY stringValue text(32766) " and checked the column size it is set to 16777215 which is the max size of the TEXT. I want to restrict it to 32766 characters. any examples will be helpful – Manoj Jun 30 '15 at 05:09

1 Answers1

0

Simple way of doing it would be to have a check constraint for field stringvalue to reject anything having length greater than 32766 but unfortunately MySQL does not support SQL check constraints. You can define them in your DDL query for compatibility reasons but they are just ignored.

There is a simple alternative

You can create BEFORE INSERT and BEFORE UPDATE triggers which either cause an error or set the field to its default value when the requirements of the data are not met.

Example for BEFORE INSERT working after MySQL 5.5

DELIMITER $$
CREATE TRIGGER `CheckLengthStringValue` BEFORE INSERT ON `attributes`
FOR EACH ROW
BEGIN
    IF CHAR_LENGTH( NEW.stringValue ) > 32766 THEN
        SIGNAL SQLSTATE '12345';
        SET MESSAGE_TEXT := 'check constraint on Attributes.stringValue failed';
    END IF;
END$$   
DELIMITER ;

Prior to MySQL 5.5 you had to cause an error, e.g. call a undefined procedure.

Abhay Chauhan
  • 404
  • 3
  • 11