Programming Forums > Web Development and Design > PHP

BAS1X · Oct 24th, 2005, 8:27 PM

Basically I have a huge 100mb wordlist of unique strings that I'll soon be entering into my database. The problem is, I also will be adding these strings after making all of them uppercase, and mySQL assumes that 'ABC' is the same as 'abc' which makes adding the same uppercase list impossibly without making it a fulltext database instead of Unique. Here in lies the problem... This database will also be receiving words from users that submit them from my site, but I don't want this database to be filled with duplicate words unless they have some different case in them like: 'abc', 'aBc', 'ABc' etc...

is there ANY way to get around this at all?

EDIT:

I also need some new way to get the size of a table without doing mysql_num_rows. I expect this database to be around 15 million unique strings when it's done, and mysql_num_rows take forever to do with only 1 million.

tempest · Oct 24th, 2005, 9:08 PM

Yes, there is a way to make it case sensitive...

(Toggle Plain Text)

create table whatever (
    "unique_string" varchar(255) BINARY UNIQUE, 
... etc etc etc

Note the use of the BINARY option...

kurifu · Oct 24th, 2005, 9:14 PM

Got to be carful with that, by using BINARY you are no longer really working with strings, more so than large blobs of byte arrays.

This will not work properly with UNICODE too, though it doesn't seem like that is much of a concern, just though I would point it out.

Oct 24th, 2005, 8:27 PM	#1
BAS1X Newbie Join Date: Oct 2005 Location: USA Posts: 1 Rep Power: 0	mySQL: Changing unique database to fulltext Basically I have a huge 100mb wordlist of unique strings that I'll soon be entering into my database. The problem is, I also will be adding these strings after making all of them uppercase, and mySQL assumes that 'ABC' is the same as 'abc' which makes adding the same uppercase list impossibly without making it a fulltext database instead of Unique. Here in lies the problem... This database will also be receiving words from users that submit them from my site, but I don't want this database to be filled with duplicate words unless they have some different case in them like: 'abc', 'aBc', 'ABc' etc... is there ANY way to get around this at all? EDIT: I also need some new way to get the size of a table without doing mysql_num_rows. I expect this database to be around 15 million unique strings when it's done, and mysql_num_rows take forever to do with only 1 million.

Oct 24th, 2005, 9:08 PM	#2
tempest Programming Guru Join Date: Oct 2004 Posts: 1,041 Rep Power: 5	Yes, there is a way to make it case sensitive... (Toggle Plain Text) create table whatever ( "unique_string" varchar(255) BINARY UNIQUE, ... etc etc etc create table whatever ( "unique_string" varchar(255) BINARY UNIQUE, ... etc etc etc Note the use of the BINARY option... __________________

Oct 24th, 2005, 9:14 PM	#3
kurifu Expert Programmer Join Date: Jul 2004 Location: Halifax, Nova Scotia (Canada) Posts: 784 Rep Power: 5	Got to be carful with that, by using BINARY you are no longer really working with strings, more so than large blobs of byte arrays. This will not work properly with UNICODE too, though it doesn't seem like that is much of a concern, just though I would point it out. __________________ Clifford Matthew Roche <geek@cliffordroche.com> Web Hosting: http://www.crd-hosting.com Consulting: http://www.crdev-consulting.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Programming Forums > Web Development and Design > PHP

mySQL: Changing unique database to fulltext