MySQL guide for SQL Server developers/DBAs - Part 1

Do you know what "My" means in MySQL?
"My" is the name of one of the co-founders Michael Widenius’s daughter.

If you are a SQL Server developer/DBA and need to switch to MySQL,
here are some useful gotchas:

	SQL Server	MySQL
Last inserted identity value	@@IDENTITY SCOPE_IDENTITY() Returns the last automatically generated value	LAST_INSERT_ID() Returns the first  (NOT LAST) automatically generated value. so, be careful when you insert multiple rows.
Nested transactions	Supported	Not supported. START TRANSACTION statement will implicitly commit any current transaction
Local variables	Supported everywhere: in SPs and in batches/scripts	There are local variables and system variables. Local variables can be used only inside SPs, you cannot use them in script/batch.
if / while statements	Supported everywhere	IF / WHILE statements are not supported in scripts/batches. They can be used only in SPs/Functions/Triggers
Alter stored procedure	alter stored procedure statement is supported	not supported, if you need to alter SP, you need to drop it and recreate it
Stored procedures parameters	Supports default values and can be named referenced	no default values and cannot be named referenced
PRINT statement	Supported	Not supported
Division by zero	select 1/0 will return an error	select 1/0 will return NULL
Temp tables	a temp table can be referenced as much as you want in a query	You cannot refer to a TEMPORARY table more than once in the same query
Row_Number()	Windows functions supported	not supported until version 8, can be emulated as SET @row_number = 0; SELECT (@row_number:=@row_number + 1) RowId
Activity monitoring	sp_who2, sp_whoisactive, sys.dm_exec_sessions, sys.dm_exec_requests	show processlist, show full processlist; SELECT * FROM information_schema.processlist; Tables under performance_schema: -events_statements_history_long - events_statements_history - events_statements_current, mysql.slow_log
Open Transactions	dbcc opentran or SELECT * FROM sys.sysprocesses WHERE open_tran = 1	select * from INFORMATION_SCHEMA.INNODB_TRX; Use this query to see which transactions are waiting and which transactions are blocking them: SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

 Enjoy :)