MySQL (5.7.38) automatically "corrects" incorrect values entered in to a smallint column without generating an error. Values larger than 32767 are automatically reduced to 32767 and blank entries automatically become 0s. Can someone please tell me what the correct way is to get MySQL to generate errors and not allow incorrect entries rather than automatically converting them? Thanks!

CodePudding user response：

You need to set the STRICT_TRANS_TABLES sql_mode. Ideally you would do this in your configuration file so it persists across database restarts, but you can also do it globally or just for a particular session:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting

Note that this will error on string truncation as well or unparsable dates/datetimes. And unfortunately in the later case, it triggers an error even if you are merely trying to detect unparsable dates, not just when you are actually trying to update or insert them into a date/datetime/timestamp column.

fiddle

You can change rather than entirely reset the mode with:

SET SESSION sql_mode = concat(@@sql_mode, ',STRICT_TRANS_TABLES');

Note that this is the default for 5.7, and you may be interested in looking at other mode settings that are now the default.