4
How do I assign 0 when the value in the column in Mysql is negative? Or is there some kind of data that only has positive numbers?
3 answers
11
Set your column to unsigned (no sign) this ensures that only positive values zero and null are valid entries.
In this example the first Insert already returns the error:
Data truncation: Out of range value for column 'n' at Row 1
Or if you check errors for warnings -1 will become 0.
create table t(
id int(11) auto_increment primary key,
n int(11) unsigned
);
insert into t (n) values(-1);
insert into t (n) values(2);
insert into t (n) values(0);
insert into t (n) values(null);
4
Complementing the other answers, if you cannot change the structure of your table to use the unsigned as mentioned in the other answers, an alternative is to use the function GREATEST.
Example (sqlfiddle):
SELECT GREATEST(id, 0) from teste;
The function will show the highest value between id and 0, that is, if the value of id is negative, will show zero.
1
To avoid using signals, use unsigned, question already answered in stackoverflow in English.
To limit the value of the column you could use Check Constraint however Mysql does not have support for this type of Constraint, hence you can create a thrigger to perform the desired behavior.
https://stackoverflow.com/questions/7522026/how-do-i-add-a-check-constraint-to-a-table
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.