There are times when you find yourself having to rename data in your MySQL database based on a specific pattern.
I had this very problem today. A designer had changed the dimensions of the thumbnails on a website and needed the data in the database updated to reflect the new path if they were stored in a specific directory.
My first thought was to write a script to iterate through every row in the database, extract the data, manipulate it, then write it back. Thankfully, warning bells rang in my head and I thought about the problem again.
MySQL offers various string manipulate routines and the
REPLACE takes a string, a substring to match, and a replacement to insert if a match occurs.
SET thumbnail_url=REPLACE(thumbnail_url, '/160x90/','/150x84/')
WHERE thumbnail_url IS NOT NULL;
This SQL goes through each row in the artists table where the thumbnail_url is not null, replacing each instance of /160×90/ with /150×84/, keeping the other data intact.
This is more of a note to myself, but blogged in the hope it will help someone else using MySQL.
I had to rename a column in a MySQL database earlier. However the column had a foreign key on it, so I was getting the dreaded error 150, warning me of a foreign key constraint.
The soltuion is to first drop the foreign key, then change the column, finally adding the constraint back in.
The SQL will look something like this…
ALTER TABLE `mytable`
DROP FOREIGN KEY `mytable_oldname_fk`,
CHANGE COLUMN oldname newname INT(11) DEFAULT NULL,
ADD CONSTRAINT `mytable_newname_fk` FOREIGN KEY (`newname`) REFERENCES `anothertable` (`id`)
I’ve been having to use a lot of utf8 recently, and being old school I still use the command line a lot.
One project has been importing a lot of international data into a mysql database.
The database is in utf8, but when I used the command line, non latin1 data was coming back corrupted.
It turned out that the command line doesn’t automatically detect the character set, so it was printing as if it was latin1. There is a flag that can be passed in called –default-character-set and this can be set to utf8. Once set, utf8 data is correctly displayed on my terminal.
mysql --default-character-set=utf8 testdatabase
This is also useful when piping in utf8 data.
I’ve used MySQL for many years and I’ve (nearly) always used
AUTO_INCREMENT to get unique primary keys for my tables.
I came across a requirement earlier today that meant that I needed an id to start from 100000, instead of the the usual 1.
Normally I’d have just created a dummy record with an id of 100000 so the next insert would occur at 100001. This works as
AUTO_INCREMENT automatically inserts the current highest value plus 1 for a column when you insert a row with NULL or 0 for the row’s value. This isn’t very elegant so I spent a few minutes with the MySQL manual and found that it is possible to seed the
AUTO_INCREMENT to start at a specific value without having to use a dummy insert.
When creating a table you add a seed value at the end of the create statement something like this…
CREATE TABLE test(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
) AUTO_INCREMENT = 100000;
This means the first insert will have an id value of 100001 (the highest current value plus 1).
You can also alter an existing table to set the
ALTER TABLE test AUTO_INCREMENT=200000;
The next insert will have an id of 200001 now.
I came across a rather cryptic MySQL error message today while working on a new database.
I had a foreign key I wanted to remove from a table due to a change in the database structure, however when I tried to remove it I kept getting the following error.
mysql> alter table band drop foreign key track_id;
ERROR 1025 (HY000): Error on rename of './rob/band' to './rob/#sql2-b0c-3588d' (errno: 152)
I thought this was a permissions problem to start with on the file structure, but in reality it’s InnoDB complaining that I can’t drop the column I’m using as the foreign key.
The solution is to drop the key first, before dropping the column.
But how do you find the name of the key?
Well for that you need to use
show create table tablename, in my case tablename was band.
Running this on my code I found my key (as show in this snippet of SQL)…
PRIMARY KEY (`id`),
KEY `track_id` (`track_id`),
CONSTRAINT `id_trackkey` FOREIGN KEY (`track_id`) REFERENCES `track` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
So I can see this is id_trackkey. The following SQL deleted the key and the column.
alter table band drop foreign key id_trackkey;
alter table band drop track_id;
Voila, the foreign key and the column are now deleted.