Mysql

Calling Mysql Iteratively and Passing Parameters from Bash Loop

Linux and MysqlBelow we see an example of calling a mysql script from a file and passing a param.

sql script

So make a file with the following (or whatever else you wish):

SET @i = 1;
use databaseName;
SET @pID=@i+100;
SET @partnerID=concat('partner',@pID);
select @i, @pID , @partnerID;

Now, this will print out the values of i, pID, and partnerID.
Below that you could include insert statements or whatever else you wish.

Now how do we call this from bash and set the @i param?

bash script

user=root
password=mysqlPass
database=db

for i in {300..400..5}
do
sed -i "1 s/.*/set @i=$i;/" sample.sql
mysql --user="$user" --password="$password" < sample.sql
done

So how does this work?
We use sed to replace the first line completely in sample.sql with a new “set @i=” command containing the i param we want.
We then call mysql and feed the rewritten file.

Another way to do this would be to concatenate the sample.sql contents to the set command, and feed them to mysql in memory.

SO:Removing duplicate rows from data pulled from db in java

I’ve become an active member of stack overflow, both answering and making questions.
It’s amazing, there’s a whole community there that I hadn’t noticed before.
As I have not had time to post new items, I will be borrowing my answers to stackoverflow questions and re posting them here.


User Question:

So here’s my question. I have a giant text file of data and I need to input all of this data into a mySQL database fast through obviously using a java program. My only problem is that, the data is identified by a certain ID. Some of these ID’s have duplicates and contain all the same info as eachother. I would like to remove all of these for sorting purposes and clarity sake.

What would be the best way to go about this? If anyone could help I’d appreciate it

Answer:

clones/duplicates

clones/duplicates

While reading the data have a hashmap or hashset. check if the id exists in the hasmap/hashset and if so continue. otherwise enter in set/map and insert.

An aside: The difference between hashmap and hashset is hashset only takes values while hashmap takes key values. However, Hashset itself uses a hashmap within memory and just inserts a dummy object for values. See: Differences between HashMap and Hashtable?

More

Remove duplicate entries from mysql table

Was writing some code, and dealt with this problem…so posting about solving this problem with 1 command (or more) depending on your decision.If you want to remove all duplicate rows with the same combination of N number of columns.

I’ll talk about two options in this post:

Add a unique key constraint that drops all duplicates

This one will remove all duplicates (based on the combination of columns you give) with one line.
The approach works by altering the table, and adding a unique key. Applying this unique key, causes all duplicate rows to be dropped leaving only one.

More