• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • CasePointer

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Sanity, thy name is not MySQL

    Jeff Boes

    By Jeff Boes
    April 10, 2014

    Probably old news, but I hit this MySQL oddity today after a long day dealing with unrelated crazy stuff and it just made me go cross-eyed:

    CREATE TABLE foo (id integer, val enum('','1'));
    INSERT INTO foo VALUES (1, '');
    INSERT INTO foo VALUES (2, '1');
    SELECT * FROM foo WHERE val = 1;

    What row do you get? I’ll wait while you second- and third-guess yourself.

    It turns out that the “enum” datatype in MySQL just translates to a set of unique integer values. In our case, that means:

    • '' == 1
    • ‘1’ == 2

    So you get the row with (1,''). Now, if that doesn’t confuse readers of your code, I don’t know what will.