Thesql error 1265 data truncated for column

MySQLpython

I created a table in mysql like this :

create table if not exists  data
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
execute_time DATETIME NOT NULL default current_timestamp,
db VARCHAR(25) NOT NULL,
book varchar(6),
`open` float(20,3) not null,
`close` float(20,3) not null)

and when I want to insert value into this table, it fails and show the exception as DataError: (1265, "Data truncated for column 'open' at row 1")

and the values I want to insert into open is 3535929.559. I code the insert data in python like this:

insertData = """INSERT INTO {table_name} (execute_time,db,book,`open`,`close`) values 
                        (%(execute_time)s,%(db)s,%(book)s,%(open)s,%(close)s)""" .format(table_name = settings["dumpTable"]["data"])

    conn.cursor().executemany(insertData,data_in_dict)
    conn.commit()

The most strange thing is when I insert the same value in MySQL workbench ,it went smoothly, the query is like this :

insert into data (db,book,`open`,`close`,execute_time) values ('au','book','3535929.559','1079339.851','2016-7-22');

But the data shown in the table is:

 '5', '2016-07-05 00:00:00', 'au', 'book', '3535929.500', '1079339.875'

I think the problem maybe insertion from python is more restricted? Then how to set it to be less restricted? Any help would be appreciated!

Best Answer

Not addressing the question, but addressing 2 bugs in your schema.

  • FLOAT(m,n) takes the decimal input, rounds to n decimal places in decimal, then converts to binary with another rounding error.
  • FLOAT (with or without (m,n)) can hold only about 7 digits of precision. Hence, both numbers in your example lose precision at the low end. As you found out, storing 3535929.559 into a FLOAT(20,3), then retrieving it will yield only 3535929.500. This is because a FLOAT has only 24 "significant bits of precision".

There are two solutions for both bugs:

  • DECIMAL(m,n) -- (20,3) will still round to 3 decimal places, but that might be OK for your application. But it does not have the extra rounding to binary nor the loss of precision.
  • DOUBLE (without (m,n)) -- This has about 16 digits of precision, more than enough for most applications. And, even though the decimal input will be rounded to binary, it will be so far out that it is unlikely to cause errors.

Sizes...

FLOAT (with or without (m,n)) takes 4 bytes.
DOUBLE (with or without (m,n)) takes 8 bytes.
DECIMAL(20,3) takes 10 bytes. Shrinking either number may save space.

Related Question