Insert ampersand into MySQL

Question

This is an extension of my question here.

In a simple test to gather more information for the question I linked to I tried to insert an ampersand character into my database through the phpMyAdmin interface...

INSERT INTO `my_db`.`some_table` (`id`, `version`, `value`) VALUES (NULL, '0', '&');

... but what gets inserted is the HTML equivalent &.

My database collation is utf8_bin, the table I'm inserting into is utf8_general_ci, and the field I'm attempting to insert the ampersand into is of type varchar.

What must I do to insert the raw ampersand character & into my MySQL database?

Update:

I don't know what the problem with phpMyAdmin is, but I think I found the problem with my application. It seems that Hibernate just wasn't using the correct character set for the connection. I already had a custom MySQL dialect specified so I just overrode the getTableTypeString method as seen below and my application now inserts values into MySQL properly:

class CustomMySQL5InnoDBDialect extends MySQL5InnoDBDialect {   
    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8"
    }
}

possible duplicate of [The best way to store &(Ampersand) in MySQL database](http://stackoverflow.com/questions/11704233/the-best-way-to-store-ampersand-in-mysql-database) — Kermit, Sep 30 '13 at 15:45
This might be a case of the web interface of phpMyAdmin messing this up for you. Have you tried running this query directly inside MySQL or via some program language? — Mike Brant, Sep 30 '13 at 15:46
I have only tried running the query through phpMyAdmin and through the connection created by my Grails application. Both yield the same result. I'll try using MySQL Workbench. — ubiquibacon, Sep 30 '13 at 15:49
@FreshPrinceOfSO The question you linked to is asking what is the best way to store an ampersand. Apparently the OP of that question knows how to store an ampersand in either format `&` or `&`. My question is asking why is the HTML format being stored when I am attempting to store a raw ampersand character `&`. — ubiquibacon, Sep 30 '13 at 15:53
I just tried this, I'm running phpMyAdmin 4.0.1-rc1. It works fine, it does not convert & to & I also tested in the MySQL command-line client, and it works the same. — Bill Karwin, Sep 30 '13 at 15:54
I think the issue is not with storing the `&` value in mysql. The issue seems to be with the retrieval. — heretolearn, Sep 30 '13 at 15:57
@MikeBrant I just wiped all data from the database then inserted an `&` using MySQL Workbench and the query in my question. The `&` character was stored, not the HTML equivalent. I then ran the same query again using phpMyAdmin and the `&` character was stored, not the HTML equivalent. Something weird is going on here. — ubiquibacon, Sep 30 '13 at 16:05
I cannot reproduce this problem either, with phpMyAdmin 4.0.7. — Marc Delisle, Sep 30 '13 at 16:05
@MarcDelisle I'm using an older version, 3.4.5 Guess it is time to upgrade. — ubiquibacon, Sep 30 '13 at 16:10
@MarcDelisle I don't know what the deal is with phpMyAdmin, but I think the character set my application was using to connect to MySQL was causing my problems. See my updated question. — ubiquibacon, Oct 01 '13 at 18:06

score 0 · Answer 1 · answered Sep 30 '13 at 15:54

0

You could try with:

INSERT INTO `my_db`.`some_table` (`id`, `version`, `value`) VALUES (NULL, '0', _utf8'&');

answered Sep 30 '13 at 15:54

Lolito

413
3
9

score 0 · Answer 2 · answered Oct 01 '13 at 18:06

I don't know what the problem with phpMyAdmin is, but I think I found the problem with my application. It seems that Hibernate just wasn't using the correct character set for the connection. I already had a custom MySQL dialect specified so I just overrode the getTableTypeString method as seen below and my application now inserts values into MySQL properly:

class CustomMySQL5InnoDBDialect extends MySQL5InnoDBDialect {
@Override public String getTableTypeString() { return " ENGINE=InnoDB DEFAULT CHARSET=utf8" } }

Insert ampersand into MySQL

2 Answers2