Geek asks and answers

Limit field length of integer data type

I am trying to limit the number of numbers a whole field can contain. For example, I want the field to contain a number no more than 5, so 99999 will be the highest allowable.

Can this be done in MySQL? I looked through the documentation but could not find the answer.

+5
source share
3 answers

Unfortunately, MySQL does not implement a restriction CHECKor custom types. Perhaps this will change in future versions.

Until then, you can use a trigger to correct the input, if this is the way for you:

delimiter //
CREATE TRIGGER trigger_check BEFORE INSERT ON your_table
FOR EACH ROW 
BEGIN 
    IF NEW.NUM > 99999 THEN 
        SET NEW.NUM = 0; 
    END IF; 
END
//
+3
source

, . , , , "" . , .

+1

This cannot be done at the database level. You will have to either use stored procedures or put this logic into your application.

http://dev.mysql.com/doc/refman/5.5/en/integer-types.html

The int facility is the closest you can get.

Also, at some point you will probably see something like this:

CREATE TABLE blah ( col INT (5) );

This does not mean what you think. This does not actually limit the length of the field to 5 places. This means that it must be 0 padded to 5 places when the column is set to zerofill.

0
source

More articles:


All Articles