INSERT succeeds but all inserted values become NULL

Question

I tired to execute an insert directly from phpMyAdmin as follows:

INSERT INTO oracle.PLAYLIST_MUSIC ( TID,
                ID,
                STATUS,
                CREATED_BY,
                CREATED_DATE,
                UPDATED_BY,
                UPDATED_DATE,
                ORDER
            )
VALUES(TID = 56919,
       ID = 115948,
       STATUS = '1',
       CREATED_BY = 15217,
       CREATED_DATE = NOW(),
       UPDATED_BY = 15217,
       UPDATED_DATE = NOW(),
       ORDER = 0)

The SQL executed successfully, however I found that all the inserted values became NULL, including the system generated values like NOW().

Does anybody have an idea?

Answer 1

Your insert syntax is wrong, using column=value in the values clause doesn't do what you think it does.

Try:

INTO oracle.PLAYLIST_MUSIC ( TID,
                ID,
                STATUS,
                CREATED_BY,
                CREATED_DATE,
                UPDATED_BY,
                UPDATED_DATE,
                `ORDER`
            )
VALUES(56919,
       115948,
       '1',
       15217,
       NOW(),
       15217,
       NOW(),
       0)

The values must be put in the same order as the column names appear in the insert statement.

It would probably be a good idea to quote the order column, since that's a reserved word.

I'm guessing the nulls come from the fact that the columns aren't bound to anything in the values clause, causing them to be null. And null = any value evaluates to null:

mysql> select null=1 ;
+--------+
| null=1 |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

But that's just a guess about what is actually happening, I'm surprised that parsed at all.

Answer 2

In MySQL you can use the following syntax:

INSERT INTO oracle.PLAYLIST_MUSIC 
SET
       TID = 56919,
       ID = 115948,
       STATUS = '1',
       CREATED_BY = 15217,
       CREATED_DATE = NOW(),
       UPDATED_BY = 15217,
       UPDATED_DATE = NOW(),
       ORDER = 0;