MySQL Query for a Programmer

I am writing some useful queries collected in past specially when I was working with Ruby & Java projects.
They are more than SIMPLE and lesser than COMPLEX and often used by a programmer. This post is more for my self reference. Good if somebody else is benefited…
To make it simple, I have wrote them with examples only rather than having a complete syntax.
You can do a browser search(Ctrl+F) within this post based on few keywords:
mysqldump, alter, add index, add column, csv to database, group concat, rename etc.

1. Copy a table : Copies a table along with data structure and data into another table:
create table trackedi_duplicate as select * from trackedi

2. Insert multiple rows using single query
INSERT INTO “table1” (“column1”, “column2”)
SELECT “column1”, “column2” FROM table2

3. Repeating the same character:
SELECT REPEAT(‘a’,5)
Outputs aaaaa

4. Trimming the \n from a long description like column
select trim(both ‘\n’ from description) from base;

5. Position of a char or string in a column :  Give me all the rows containing the word “pro” in status column
select * from trackedi where LOCATE(‘pro’,status) > 0

6. Size of each table
SELECT TABLE_SCHEMA AS ‘Database’, TABLE_NAME AS ‘Table’,
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),15),” Mb”) AS Size FROM INFORMATION_SCHEMA.TABLES;

7. Loading data from CSV to a table
LOAD DATA LOCAL INFILE ‘C:\\dbs_dump\\mytable1.csv’ INTO TABLE myTable1 FIELDS  TERMINATED BY ‘,’ LINES TERMINATED BY ‘\r\n’;

8. Dump a database
mysqldump -u root -p testDb  > c:\dbs_dump\testDb.sql

9. Dump few tables only
mysqldump -u root -p testDb table1 table2 > c:\dbs_dump\fewTables.sql // table structure along with data
mysqldump -u root -p -d testDb table1  table2 > c:\fewTablesStructure.sql // table structure without data

 10. Storing/Running a sql scriptMake sure that you are at the prompt of mysql
mysql> source fewTables.sql

11. Renaming a table
rename table analytical_charts to data_charts;

 12. Dropping/Adding a foreign key
alter table data_analysis drop foreign key chart_id;
alter table data_analysis add foreign key(`chart_id`) REFERENCES `data_charts` (`id`);

13. Adding a new column
alter table data_charts add column `chart_type` varchar(80);

 14. Adding a new index
alter table data_charts add index `chart_type` (`chart_type`);

15. Updating a column by string replacement
update [table_name] set [field_name] = replace([field_name],'[string_to_find]’,'[string_to_replace]’);
update myTable set myColm1 = replace(address,’\”,”);

16. Group Concat – Give me the list of all names grouped by status – MySQL specific, you don’t have in SQLServer
SELECT status, GROUP_CONCAT(name) FROM trackedi group by status

Live Situation #2 : Return value from Stored Procedure

Sometimes a problem sounds the damn easiest, but we get trapped into a really weird situation.

Because we just try to solve these problems by relating them with a similar concept. The technology resemblance is not so simple.
Problems may be as easier as to just add two numbers. There could be a number of technical approaches. Somebody just write a C function, somebody using pointers, somebody can play around the address values to display the same result value or someone using built-in API.

In this ORM world of programming (Object Relationship Mapping – Hibernate, Active Records etc.), the framework is giving a flexibility to a programmer to treat the entire database tuple (rows) as just like basic data types variable. And hence we over mistakes ourselves assuming they behave exactly.

Recently we had an issue with a Biztalk project around SQL Server.

Situation was like to retrieve the party name from one table in SQL Server. Later on based on the party name you need to populate another table.

currentParty = getPartyName();
if(null != currentParty ){
   // Populate the other table
}

We wrote a stored procedure pretty simple to above. But that did not work.
This is not the right place to explore each byte of our experiment; but finally what we concluded is – HOW TO CALL A SP, HOW TO PASS ARGUMENTS TO IT, HOW TO RETRIEVE PARAMETERS.

If you are a C#, Java, Ruby etc developers – You think this is pretty simple. But that’s not the case.
Don’ttreat the store procedure as normal functions. Syntactically stored procedures differs a lot.

1. Below is an example on – HOW TO RETURN VALUE from a SP

CREATE PROCEDURE doAddition_Using_ReturnValue
AS
BEGIN
      DECLARE @retRes int;
      --SET @retRes  = exec getSum_As_ReturnValue 4,5
      exec @retRes = getSum_As_ReturnValue 4,5
      print @retRes
END
GO

CREATE PROCEDURE getSum_As_ReturnValue
      -- Add the parameters for the stored procedure here
      @n1 int, @n2 int --// No special keyword for input paramaters
AS
BEGIN
      declare @res int
      set @res = @n1 + @n2;
      return @res
END

Your SP call should be – exec doAddition_Using_ReturnValue;

So what a big deal here. The big deal is hereYour return statement is compatible with int data type only. What if you want to return a string value and capture into a variable? As in my case party name has to be a string.

The answer is using OUTPUT parameter and that happens to be using a “SELECT” in SP.

2. Below is an example on How to return value using SELECT as OPUTPUT parameter

CREATE PROCEDURE doAddition_Using_OutParameter
AS
BEGIN
      DECLARE @outRes int;
      exec getSum_As_OutParameter 4,5, @outRes OUTPUT --// @outRes holds the returned value getSum_As_OutParameter having two in parameters 4,5
      print @outRes
END
GO

CREATE PROCEDURE getSum_As_OutParameter
      -- Add the parameters for the stored procedure here
      @n1 int, @n2 int, @res int OUTPUT   // @res must come in a SELECT statement for returning purpose
AS
BEGIN
      set @res = @n1 + @n2;
      SELECT @res
END
GO

Special thanks to Mario for letting me know that SELECT and return are different in SP calls.
Thanks to Biztalk friends here – Sandeep, Som & Manish for trapping me into a silly(sorry toughest!) problem.

Live Situation #1 : Using a metachar in SQL query

Problem – Fetching the metachar from a database table in MSSQL Server
Scenario – In one of the Biztalk project closely executed by my friend had one issue in generating and processing an EDI file.

1. Extract member information data from SQL Server via SQLAdapter in Biztalk
2. Have some stored procedure calls
3. Create EDI and sends it to the send port

There were 18000 records in sql table and is expected to have an EDI of 18000 claims. Entire process was moving smoothly so far.
Suddenly Biztalk threw an error and it was difficult to predict what exactly the error is”.

He made a small change in SP to fetch only 100 records and run the entire process. He succeeded into it and could generate a proper EDI.
He changed the SP to 1000 then 2000, 3000, 4000. Worked well. But it failed in case of SP fetching 5000 records.

My dear friend suspected that there is a data issue between 4000 to 5000. But what exactly? After little normalization we felt it must be an issue of some wild chars (., *. # as they may appear in member address field).

So how can we identify them?

We planned to put a plan SELECT query as below
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘*’, address) > 0 //incorrect

This gives you a syntax error. We modified it by putting an escape char
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘\*’, address) > 0 // incorrect

Modified to – escape the \ char as well
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘\\*’, address) > 0 // incorrect

Solution:
Then finally we stored the ASCII value of it in a variable and run it as below

GO
Declare @findMetaChar nvarchar(5);
Set @findMetaChar = char(42)
SELECT top 9000 address FROM dataMembers
where CHARINDEX(@findMetaChar, address) > 0
GO

This listed us 3 rows and observered as there were * chars in address field.
And in Biztalk, * we took as segment element separator. Hence this caused Biztalk to stop the processing.