Home / Questions / 55658
Accepted
Sergio Romero
Asked: 2020-03-16 14:07:54 +0800 CST 2020-03-16 14:07:54 +0800 CST

Why does MySQL allow text to be inserted into an integer(INT) field?

  • 772

Why does MySQL allow text to be inserted into an integer(INT) field?

For example, I have this:

INSERT INTO `estudiante` (`usuarioID`, `identidad`, `correo`, `institucion`)
               VALUES ("32", 'xxx', '[email protected]', 'exterior')

the second field is identity which is integer because it doesn't give me an error when inserting a text

What am I doing wrong? it's supposed to be an int it shouldn't allow text, it converts it to zero

Here is the table layout:

    CREATE TABLE `estudiante` (
    `usuarioID` INT(11) NOT NULL,
    `identidad` INT(20) NOT NULL,
    `correo` VARCHAR(40) NOT NULL,
    `institucion` VARCHAR(80) NOT NULL,
    PRIMARY KEY (`usuarioID`)
)
mysql

2 Answers

  1. Best Answer

    The behavior you observe is described in the documentation here :

    By default, MySQL is forgiving of invalid or improper data values ​​and coerces them to valid values ​​for data entry. [...]

    If you are not using strict mode, then whenever you insert an “incorrect” value into a column, [...] MySQL sets the column to the “best possible value” instead of producing an error

    If you try to store a string that does not start with a number into a numeric column, MySQL Server stores 0.

    (Poor) translation:

    By default, MySQL forgives you for invalid or inappropriate data input and forcibly converts (twists) it to valid values ​​in order to insert them. [...]

    If you are not using strict mode, then when you insert a "wrong" value into a column, [...] MySQL assigns the "best possible value" instead of producing an error.

    If you try to assign a string (text) that doesn't start with a number to a column of type numeric, MySQL assigns a 0.

    Side note, this is one of several reasons I don't have much respect for MySQL as a database. If you care to read the entire page, it's amazing how MySQL, by default, is willing to twist/convert data in order to avoid giving you an error. What they call doing you a favor, I call data corruption.

    In any case, applying the documented rule, this means that MySQL inserts and converts the following strings like this:

    'xxx' --> 0
'xxx12' --> 0
'12xxx' --> 12

    Demo

    If, like me, you don't find this behavior correct, you can modify the way MySQL handles these situations. Again, as the documentation says :

    You can enable strict SQL mode to select more traditional treatment of bad values ​​such that the server rejects them and aborts the statement in which they occur.

    You can select stricter treatment of input values ​​by using the STRICT_TRANS_TABLESor STRICT_ALL_TABLESSQL modes

    (Poor) translation:

    You can activate SQL strict mode to choose a more traditional handling of incorrect values ​​so that the server rejects them and cancels the statement where they occur.

    You can select stricter handling of input values ​​using the SQL STRICT_TRANS_TABLESor STRICT_ALL_TABLES.

    And, in fact, if you make sure that sql_modeyour session includes STRICT_TRANS_TABLES, you will see that MySQL will throw an error.

    Demo

    To see the value you have for sql_mode:

    select @@sql_mode;

    Example of how to modify sql_modeyour session:

    SET sql_mode = 'STRICT_TRANS_TABLES';

    For more information about SQL modes you can follow the following link: Server SQL Modes .

    • 7

  2. In those cases MySQL does a conversion:

    You can specify an expression expr to provide a column value. This might involve type conversion if the type of the expression does not match the type of the column, and conversion of a given value can result in different inserted values ​​depending on the data type. For example, inserting the string '1999.0e-2' into an INT, FLOAT, DECIMAL(10,6), or YEAR column results in the values ​​1999, 19.9921, 19.992100, and 1999 being inserted, respectively. The reason the value stored in the INT and YEAR columns is 1999 is that the string-to-integer conversion looks only at as much of the initial part of the string as may be considered a valid integer or year. For the floating-point and fixed-point columns, the string-to-floating-point conversion considers the entire string a valid floating-point value.

    I mean:

    You can specify an expression exprto provide a column value. This may involve type conversion if the type of the expression does not match the type of the column, and conversion of a given value may result in different values ​​being inserted depending on the data type. For example, inserting the string '1999.0e-2' into an INT , FLOAT , DECIMAL (10,6), or YEAR column enters the values ​​1999 , 19.9921, 19.992100 , and 1999 respectively. The reason why the value stored in the INT and YEAR columns is 1999 is that the conversion from string to integer seems only in the initial part of the string that can be considered a valid integer or year. For floating-point and fixed-point columns, string-to-floating-point conversion considers the entire string a valid floating-point value.

    Source: MySQL Reference Manual

    • 4