How restrict mysql tiny-int to accept just integer

Question

Im trying to get eeror about invalid data for a query but it seems mysql accept strings as tiney in queries and set 0 instead of returning any error.

So how can I restrict it to just accept integers.

Thanks in advance.

Mysql will take leading integers in a string and discard the rest - this is documented behaviour, if you want to prevent it catch in your front end or in a trigger. — P.Salmon
– P.Salmon, Commented Jan 26, 2021 at 11:32

Akina · Accepted Answer · 2021-01-26 11:54:43Z

1

This is SQL Mode-dependent.

In strict mode you must receive either 'Data truncated' (inserted value starts from numeric chars) or 'Incorrect integer value' (first symbol is not numeric) error message, and none data must be inserted.

If strict mode is disabled then warning is generated instead of error, and the value is converted to numeric (all symbols starting from first non-numeric one are truncated, then the value is converted to number, if it is empty string then zero is assigned).

Data type checking (and convertion if needed) is performed firstly, even before BEFORE INSERT trigger.

DEMO

answered Jan 26, 2021 at 11:54

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amirmohammad Farhang Over a year ago

I enabled it but problem is still remains.

Akina Over a year ago

@AmirmohammadFarhang Reproduce your issue on the online fidle and provide the link. 'll look...

Collectives™ on Stack Overflow

How restrict mysql tiny-int to accept just integer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related