MySQL and phMyAdmin-CodePudding

I'm new to this website and using Mysql and phpMyAdmin. I need help with one of my table and I would really appreciate it. So, I created a table that has an Integer column I want to be able to limit it to only 7(Seven) digits I'm not quiet sure if this is possible using Mysql or phpMyAdmin.

I haven't tried any query on it. I want to limit the Integer type to only 7(Seven) digits.

CodePudding user response：

This might not be the best possible solution but I think that if you were to store the integer as string in the format char(7) to limit the number of characters able to be entered it would get the job done. I'm not familiar with Mysql in particular but here's some documentation on it : https://dev.mysql.com/doc/refman/8.0/en/char.html

I hope this helped.

CodePudding user response：

In MySQL <8.0.16 You can't restrict the number of digits for an Integer. That has no meaning.

You can, however, use a DECIMAL type that allows you to specify the number of digits and the number of decimal places.

For example, DECIMAL(7,0) will define what you want.

Your CREATE statement becomes something like

CREATE TABLE IF NOT EXISTS myTable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    someText VARCHAR(255) NOT NULL,
    decimalValue DECIMAL(7,0)
)  ;

If you're using MySQL 8.0.16 or later you can use a CHECK constraint to limit the value (as distinct from limiting the number of digits).

The example above becomes

CREATE TABLE IF NOT EXISTS myTable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    someText VARCHAR(255) NOT NULL,
    decimalValue INT,
    CONSTRAINT `decValue_chk` CHECK (`decimalValue` <= 9999999))
)  ;