#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10

    Alter utf8 column to Binary w/out destroying data.


    What is the correct ALTER statement to change utf8 column to be BINARY type as well without murdering the data?

    If I had made the CREATE statement correct in the first place the column would look like this:

    "`myCol` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, "

    I've tried the following which either error or destroy data:

    alter table word change myCol myCol binary;
    alter table word change myCol myCol binary(50);
    alter table word change myCol collate=utf8-bin;
    Thanks,
    Dan

    Operating system ubuntu 12.04
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Code:
    WHERE BINARY name = 'the'
    converting the column to binary is not necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10
    Originally Posted by r937
    Code:
    WHERE BINARY name = 'the'
    converting the column to binary is not necessary

    Unfortunately it is. I'm working with drupal Views and having to always add that little "BINARY" into the query would be an unnecessary pain. A table with the correct definition is the best solution. So, do you know what that ALTER query should be? Or will I have to re-insert all my data.
    Thanks,
    Dan

    Operating system ubuntu 12.04
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by dano2
    Or will I have to re-insert all my data.
    create a new table with the desired datatype

    use INSERT INTO with SELECT option to retrieve from existing table

    drop existing table

    rename new table to original name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    139
    Rep Power
    10
    Originally Posted by r937
    create a new table with the desired datatype

    use INSERT INTO with SELECT option to retrieve from existing table

    drop existing table

    rename new table to original name
    I usually don't say "thanks" so as to not bump out somebody's question. But I want to give a macro thanks to you r937 for all the help you've given everyone over the years. You're awesome. I also found your page here interesting. http://rudy.ca/my-toronto.html It's nice to see people still making their own personal pages instead of always resorting to face book. You just have to add a picture of that lovely DVP traffic that visitors like myself can always count on being there like an old friend
    Thanks,
    Dan

    Operating system ubuntu 12.04
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    thanks for the kind words, dano

    much appreciated

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo