Tag Archives: mysql

CS 601 – MySQL INSERT … SET syntax

by

While working on my project, I came across the following. The standard syntax for inserting items into a database is something akin to:

INSERT INTO tblMyTable (FirstName, LastName, Email, Phone)
     VALUES ('John', 'Doe', 'johndoe@gmail.com', '1234567890')

This is standard SQL. However, MySQL has an alternative syntax using SET which is similar to the UPDATE syntax:

INSERT INTO tblMyTable SET
       FirstName = 'John',
       LastName  = 'Doe',
       Email = 'johndoe@gmail.com',
       Phone = '1234567890'

I ended up using the alternative syntax throughout my project. Why? I felt it is much cleaner and easier to read than the standard syntax, especially when removing a column to insert. (When adding a column, it’s easy enough to add them to the end of the lists.) It should also greatly reduce errors from incorrect ordering between column names and values since they’re right next to each other. I realize that using MySQL specific syntax would break the site if it was moved to another database, but I don’t feel that was a concern for this project. In the real world, especially dealing with projects that are commercial and may/will grow larger, it might not be a good idea.

Here’s an example from my actual code:

$query = <<<EOT
    INSERT INTO tblReservations SET
        dtReservationDateTime = FROM_UNIXTIME($datetimeunix),
        intPartySize = {$_POST['numparty']},
        intCustomerID = "{$_POST['userid']}",
        vcReservationNameFirst = "{$_POST['firstname']}",
        vcReservationNameLast = "{$_POST['lastname']}",
        vcReservationEmail = "{$_POST['email']}",
        chReservationPhone = $phone,
        vcComments = "{$_POST['comment']}",
        dtReservationDateTimeMade = NOW()
EOT;

CS 601 – MySQL enum vs. reference table

by

During last nights class, I asked Professor Sheehan about using the enum MySQL data type vs using a reference table. The MySQL enum type is basically a simple list of valid values. For instance, in an employee database, we might have a number of employee types, manager, clerk, janitor, IT. We could then assign an employee one of these values. A reference table accomplishes the same task, but with a second table:

The question is, which is better? Professor Sheehan said he doesn’t use the enum type. I did some searching and found that reference table is much preferred method for a number of reasons most related to needing to change the allowed values. Even if you think you’re never going to have to do this, you never know. Here’s a few of the sites I found:

  1. 8 Reasons Why MySQL’s ENUM Data Type Is Evil
  2. MySQL Forums :: Newbie :: using enum versus tiny int and lookup table
  3. Mysql ENUM type vs join tables
  4. Enum Fields VS Varchar VS Int + Joined table: What is Faster? – Shows that in most cases, the performance loss when using reference tables is minimal.