1

I believe this is a basic question about data type in MySQL but I can't find answer ...

I have a column defined like this: CD4: max N4, R

N4 means that this column hold numeric values which has 4 numbers and R (required) means that it can't be NULL

max N4 means that the column could hold a value with 4 characters or less.

How can I declare such a column?

I think if I declare the column to be: CD4 mediumint(4) not null then the value stored in the column must have 4 character right?

Trung Bún
  • 1,117
  • 5
  • 22
  • 47
  • The 4 in Mediuint merely states that this column will need 4 bytes to store the integer. There is no limit set regarding admissible input. – Carsten Massmann Jul 16 '15 at 08:34
  • If 4 means decimal digits then your "type" must hold values `1000..9999` and thus can not be declared in standard way ("check" option is not working in MySQL even though you can declare it, it's only for compatibility). However, you can use triggers on update/insert to check your data – Alma Do Jul 16 '15 at 08:41
  • From the documentation, it says 4 is 4 bytes https://dev.mysql.com/doc/refman/5.5/en/integer-types.html Then is there anyway to limit number of digits? Should I declare the type for this column to be `varchar(4)` ? – Trung Bún Jul 16 '15 at 08:42
  • Very related: [Difference between “int” and “int(3)” data types in my sql](http://stackoverflow.com/questions/5562322/difference-between-int-and-int3-data-types-in-my-sql) – GolezTrol Jul 16 '15 at 08:43
  • For integer types you can not "select how many digits" it will hold. Integer types are counting bytes, not digits - but even so you can not freely choose how many bytes will your type have. There are only standard options (int, bigint etc). "Digits" in integer types declarations have sense only for zerofill-defined type and declare how many positions will be displayed in such case – Alma Do Jul 16 '15 at 08:44
  • Alma Do already mentioned triggers. Here is another SO post that might be helpful in that context: http://stackoverflow.com/questions/20225991/limit-integer-value-for-mysqls-column – Carsten Massmann Jul 16 '15 at 08:45
  • MySQL Documentation on [*Numeric Type Attributes*](http://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html) – Ravinder Reddy Jul 16 '15 at 08:47

0 Answers0