MySQL Question

[pornask]

I have recently had a situation with my MySQL database on a busy site and had to import a back up version of a database. Because of the size of a database, the import took about 10 hours. After it was done, I've noticed that apparently wrong character set was chosen, hence some of special characters, such as the apostrophe, double quotes, hyphens and the such are showing as an array of random characters.

Is there a way to find a replace certain character strings with different strings in MySQL? For example this "–" should be replaced with this "-"

Is there a way to batch process the whole database with find a replace?

[pornask]

In PHPMyAdmin there's that tab named "Query" that looks like there could be a way to do it form there. But I'm not sure. Anyone? Help

[justFred]

In PHPMyAdmin there's that tab named "Query" that looks like there could be a way to do it form there. But I'm not sure. Anyone? Help

you're more than likely to be more successful just modifying the character set, but I don't really know how that affects your data.

you could try this one though...you say you have a backup of this data, correct? If not... make one. and then just to be extra safe copy the table

CREATE TABLE {newtable} AS
SELECT *
FROM {table};

then:

Update {newtable}
Set {column} = replace({column}, "?", "-");

(replace {table} {newtable} and {column} with the correct names)

[brandonstills]

If I understand your problem correctly a good text editor should allow you to replace any arbitrary string with another. If you modified the import script first and then fed it to MySQL that should do the trick. Let me know if I misunderstood the problem.

[Hell House Vic]

yeah, like brandon suggested, I would open the SQL file in a texteditor like bbEdit or whatever is a good text editor and search and replace the character set to whatever you need it to be.

also, 10 hours!?!??

maybe uploading and using mysql -u username -ppassword database < filename command from the commandline would speed that up?

[tom3k]

yea there could be a number of issues wrong here... and more info would be needed to point you in the right direction...

first and foremost, how did you dump the database in the first place?

via mysqldump? or some 3rd party tool?

as for 10 hours... unless you have an absolutely MASSIVE database with millions of rows (and i mean MANY millions of rows) this is taking waaay too long to import...

how are you importing you dumpfile?

[glow]

Theres something called BigDump, incase anyone is not aware of it, that can be useful in general when uploading MySQL db's