Showing entries 11 to 12
« 10 Newer Entries
Displaying posts with tag: tricks (reset)
Adding a value to enum Column - A Dirty Hack

It is the first time I am going to write something serious after registering this domain. Probably I was too lazy to blog or I was working hard that I had no time to write blogs.

Any way I have decided to break the silence

Today I came across a problem where I had to add a new value to an enum column in a table with a few million rows. The traditional method of getting that done will take ages to finish and I could not afford to have a such a long window. Fortunately for me I was dealing with a MyISAM table and I had a dirty trick in mind ]:< .

I will list out a small example showing how I did it:

Suppose my table is

create table a( id int,
choice enum('agree','disagree')
)Engine=MyISAM

and I want add a new choice ‘ Don’t care’ what I will do is , I will create a new table …

[Read more]
MySQL: Replacing URL Escape Sequences

So you want to store URLs in MySQL, and the URLs have those annoying %20%27%7C%26%5E%2B%2D%25 symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:

%20 - space
%27 - '
%7C - |
%26 - &
%5E - ^
%2B - +
%2D - -
%25 - %

So, how about we do some search’n’replace on that?

mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @url as original,
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        @test,'%20',' '),
    ->        '%27','\''),
    ->        '%7C','|'),  -- REPLACE() is case sensitive
    ->        '%7c','|'),  -- so we have 
    ->        '%26','&'),
    ->        '%5E','^'),
    ->        '%5e','^'),  -- …
[Read more]
Showing entries 11 to 12
« 10 Newer Entries