Friday, April 29, 2011

Cannot insert string into MySQL text column

For some reason, my queries screw up when I write to a column of type "text". Here is an example:

Describe messages;

Format is: Field Type Null Key Default

id  int(11) NO PRI NULL auto_increment
title   varchar(255) YES  NULL 
body    text YES  NULL 
to  text YES  NULL 
content_type    varchar(255) YES  NULL 
is_sms  tinyint(1) YES  NULL 
user_id int(11) YES  NULL 
created_at  datetime YES  NULL 
updated_at  datetime YES  NULL

Then I try an insert:

INSERT INTO messages (id,title,body,to) VALUES ('1','Test Message','This is a test message. This is a test message. This is a test message. This is a test message.', 'an email' );

For some reason this causes a general MySQL syntax error. The query works fine if I remove the "to" column and it's corresponding value from the query.

Any ideas?

From stackoverflow
  • 'to' is a reserved keyword in MySQL. You'll need to rename your column.

    http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

    However, Reserved words are permitted as identifiers if you quote them.

    http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

  • INSERT
    INTO     messages (id,title,body,`to`)
    VALUES   ('1','Test Message','This is a test message. This is a test message. This is a test message. This is a test message.', 'an email' );
    
  • Try this instead

    INSERT INTO messages (`id`,`title`,`body`,`to`) 
       VALUES ('1','Test Message','This is a test message. 
       This is a test message. This is a test message. This is a test message.', 
       'an email' );
    
  • I believe if you surround the "to" with backtics like so:

    INSERT INTO messages (id,title,body,`to`) VALUES ('1','Test Message','This is a test message. This is a test message. This is a test message. This is a test message.', 'an email' );
    

    it will work - did for me anyway.

0 comments:

Post a Comment