title: Mysql update: ERROR 1292 (22007): Truncated incorrect DOUBLE value
body: <p>My problem is the following:
  I have a table <code>foo_table</code> with id column <code>ID</code> as <code>bigint (primary key)</code>, name column <code>NAME</code> as <code>varchar(80)</code> and code COLUMN code as <code>varchar(20)</code>. I have a unique constraint on the CODE column.</p>
  
  <p>If I fill this table with only "number" strings in the code column, and then one day decide to add a row with a, surprise, "string" string in that code column, say putting "My Code", it works fine ! The error comes when I tried updating one of my previous records, one that has a CODE value being a number; in this case I get the famous error saying:</p>
  
  <blockquote>
    <p>ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'My Code'.</p>
  </blockquote>
  
  <p>I'm not even touching that record ! I presume MySQL is trying to check the unique constraint and try to convert every value in the CODE column to double, but that's insane, and very unprofessional.</p>
  
  <p>Anyone has a solution ? Like turning off string&lt;->number conversion in MySQL ? </p>
  
  <p>Thanks in advance.</p>
  
  <p>Edit:
  Ok I have the sample code, but while creating it I saw how to fix the problem, which is still a problem in MySQL as far as I'm concerned:</p>
  
  <pre><code>CREATE TABLE foo_table (
  ID bigint(20) NOT NULL DEFAULT '0', 
  NAME varchar(80) DEFAULT NULL,
  CODE varchar(20) NOT NULL DEFAULT '', 
  UNIQUE KEY foo_table_uk (CODE)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;
  
  insert into foo_table (id, name, code) values (1, 'one', '1');
  insert into foo_table (id, name, code) values (2, 'two', '2');
  insert into foo_table (id, name, code) values (3, 'three', 'three');
  
  update foo_table set name='a-one', code='1' where code=1; &lt;-- This update throws the error
  
  update foo_table set name='a-one', code='1' where code='1'; &lt;-- this update works fine
  </code></pre>