MYSQL server post tips

MY SQL
********
 post in server with restriction  use following

in connection string use the following code  also
use procedure bodies=false //the user who not have the execution rights in the server for tables, procedure etc
allow user variables =true //for retriving values from db that is return in my sql use this code

Example connection string is
 <connectionStrings>    
    <add name="STudConStr" connectionString="Server=ipaddress;Port=3306;Database=dbname;Uid=username;Pwd=password; use procedure bodies=false; allow user variables =true" providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>

Example procedure in mysql
***********************
 DELIMITER $$
CREATE DEFINER=`username of db`@`server ip` PROCEDURE `spGetTotQuestionCount`()

BEGIN
    SELECT count(distinct QuestionId) as QuestionCount
from AnswerMaster ;
END$$
DELIMITER ;


Some of diffence from ms sql VS My sql are as follows

MsSQL	MySql
1)Use Identity(1,1) Example CREATE TABLE animals (      id int IDENTITY(1,1) NOT NULL,      name CHAR(30) NOT NULL,      PRIMARY KEY (id) ); INSERT INTO animals (name) VALUES     ('dog'),('cat'),('penguin'),     ('lax'),('whale'),('ostrich'); SELECT * FROM animals; Which returns: +----+---------+ | id | name    | +----+---------+ |  1 | dog     | |  2 | cat     | |  3 | penguin | |  4 | lax     | |  5 | whale   | |  6 | ostrich |	use AUTO_INCREMENT Example CREATE TABLE animals ( id int NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO animals (name) VALUES                          ('dog'),('cat'),('penguin'),                          ('lax'),('whale'),('ostrich'); SELECT * FROM animals; Which returns: +----+---------+ | id | name    | +----+---------+ |  1 | dog     | |  2 | cat     | |  3 | penguin | |  4 | lax     | |  5 | whale   | |  6 | ostrich | +----+---------+
2)nvarchar(max)	longtext
For saving image  data type 3)Image	LONGBLOB
For creating procedure 4) CREATE PROCEDURE spGetTotQuestionCount As BEGIN                                 SELECT count(distinct QuestionId) as QuestionCount from AnswerMaster END	CREATE PROCEDURE spGetTotQuestionCount() BEGIN                                 SELECT count(distinct QuestionId) as QuestionCount from AnswerMaster END
For parameterized procedure 5) CREATE PROCEDURE spGetTot (@a int)	Create procedure spGetTot( IN a int)
declaration 6)declare @a int,@b int=0;	Declare a int; Declare b int; Set b=0;
For if condition 7)if(@a>@b) Begin Set @a=100; end	if(a>b) then Begin Set a=100; End End if
For while condition 8)while(@a>@b) Begin @a=100; end	while(a>b) then Begin a=100; end end while
9) For set default  constrains ALTER TABLE UserExamResult ADD  DEFAULT ((0.0)) FOR TotalMarks	ALTER TABLE UserExamResult alter TotalMarks set DEFAULT 0.0 ;

 

 The best way is user can download the tool for converting ms sql to mysql and convert it or else use mysql migration tool to convert table.This helps the user to get Database with value