Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder jfreak53's Avatar
    Join Date
    May 2004
    Location
    Guatemala
    Posts
    477
    Thanks
    19
    Thanked 10 Times in 10 Posts

    MySQL VarChar Primary Key upper case and lower case

    I am in the middle of copying an old DB from a system on MSSQL to a new system on MySQL DB type.

    Their old tables had a column as primary key that was 25 char varchar column. It works fine but I noticed there are 5 duplicate rows base on the fact that in the old MSSQL they had all caps on one and all smalls on the other. In MySQL it doesn't understand this is seems, and it marks it as duplicate. Is this true, is there a way around it? Thanks.
    "FORTRAN is not a language. It's a way of turning a multi-million dollar mainframe, into a $50 programmable scientific calculator."
    http://www.microfastcat.com -- FastCat Software, the fastest software on the NET!
    http://www.microthosting.com -- Free reseller web hosting, Hosting, VPS, FREE SMALL HOSTING!!!
    http://www.microtronix-tech.com -- Web design and programming

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Sure...you just have to tell MySQL that the field in question *IS* case sensitive.

    One easy way to do it:
    Code:
    CREATE TABLE tablename (
        fieldname VARCHAR(25) BINARY PRIMARY KEY,
        other ...,
        fields ... 
    );
    Adding the keyword BINARY to the VARCHAR type cause it to become case sensitive.

    Incidentally, you *DID* have to do the equivalent when you used SQL Server. I forget you specify case sensitivity in SQL Server, but it is also case insensitive by default. (In fact, the ANSI standard for SQL says VARCHAR and CHAR are case insensitive by default. The standard doesn't even provide a way of specifying that a field is case sensitive but leaves that up to each DB as an option in implementation.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder jfreak53's Avatar
    Join Date
    May 2004
    Location
    Guatemala
    Posts
    477
    Thanks
    19
    Thanked 10 Times in 10 Posts
    AHA, thanks Ped! Didn't know that one, I didn't setup the original SQL DB so I didn't know. Learn somethin new everyday
    "FORTRAN is not a language. It's a way of turning a multi-million dollar mainframe, into a $50 programmable scientific calculator."
    http://www.microfastcat.com -- FastCat Software, the fastest software on the NET!
    http://www.microthosting.com -- Free reseller web hosting, Hosting, VPS, FREE SMALL HOSTING!!!
    http://www.microtronix-tech.com -- Web design and programming


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •