Some common syntax differences between T-SQL and MySQL

This article will focus on some of the common syntax differences between MySQL and SQL Server, as well as the related content of common stored procedures. 1. identifier qualifier SQL Server[]MYSQL "2. String addition SQL Server directly with+Mysqlconcat ()3.IsNull() SQL ServerIsNull() Mysqlifnull () Note: MySQL also has a isnull () function, but the meaning is different4.getdate() SQL Servergetdate() Mysqlnow ()5.newid() SQL Server

newid () Mysqluuid ()

6 @ @ROWCOUNT SQL Server @ @ROWCOUNT  Insert, delete valid 7scope_identity() SQL Serverscope_ IDENTITY() mysqllast_insert_id ()8ifelse  ... SQL Server

IFboolean_expression{sql_statement|Statement_block}[else{sql_statement | statement_block}] --to define a statement block, use the control flow keyword BEGIN and END. MYSQLIFSearch_condition Thenstatement_list[ELSEIF search_condition then statement_list]...[ELSE statement_list]END IFNote: for MySQL, Then,Endthe IF is required. Similar to other flow control statements, this is not listed here. 9.DeclareIn fact, SQL Server and MySQL have this statement to define variables, but the difference is: in MySQL, declare is only used in the begin ... End Compound statement, and must precede any other statement at the beginning of the compound statement. This requirement will feel very BT when writing the cursor.Ten. How cursors are spelled SQL ServerDeclare @tempShoppingCart Table(ProductIdint, Quantityint)Insert  into @tempShoppingCart(ProductId, Quantity)SelectProductId, Quantity fromShoppingCartwhereUserguid= @UserGuid  Declare @productId intDeclare @quantity intDeclareTempcartcursorcursor  forSelectProductId, Quantity from @tempShoppingCart OpenTempcartcursorFetch Next  fromTempcartcursor into @productId,@quantity while @ @FETCH_STATUS = 0beginUpdateProductSetSellcount=Sellcount+ @quantity whereProductId= @productId Fetch Next  fromTempcartcursor into @productId,@quantity End CloseTempcartcursordeallocatetempcartcursor MySqlDeclareM_doneint default 0;DeclareM_sectionidint;DeclareM_newsidint; Declare_cursor_sncursor  for SelectSectionID, NewSID from_TEMP_SN;Declare ContinueHandler for  notFoundSetM_done= 1; Create Temporary Table_TEMP_SNSelectSectionID, NewSID fromSectionnewsGroup  bySectionID, NewSID having Count(*)> 1; Open_cursor_sn; while(M_done= 0) doFetch_cursor_sn intoM_sectionid, M_newsid;if(M_done= 0) Then--the specific processing logicEnd if;End  while;Close_cursor_sn;Drop Table_TEMP_SN; Note: To improve performance, you typically open a cursor on a table variable and do not open the cursor directly on the data table.  One. Paging the processing of SQL ServerCreate procedureGetproductbycategoryid (@CategoryID int,@PageIndex int = 0,@PageSize int =  -,@TotalRecords intoutput) asbegin Declare @ResultTable Table(RowIndexint, ProductIDint, ProductNamenvarchar( -), CategoryIDint, Unitnvarchar(Ten), UnitPrice Money, Quantityint); Insert  into @ResultTableSelectRow_number () Over(Order  byProductIDASC) asRowIndex, P.productid, P.productname, P.categoryid, P.unit, P.unitprice, p.quantity fromProducts asPwhereCategoryID= @CategoryID; Select @TotalRecords = Count(*) from @ResultTable; Select * from @ResultTablewhereRowIndex>(@PageSize * @PageIndex) andRowIndex<=(@PageSize *(@PageIndex+1)); EndAnd, of course, this is not the only way SQL Server is written, but it's more common. MYSQLCreate procedureGetproductsbycategoryid (inch_categoryidint,inch_pageindexint,inch_pagesizeint, out _totalreccountint)begin Set @categoryId =_categoryid;Set @startRow =_pageindex*_pagesize;Set @pageSize =_pagesize;PreparePagesql from'Select Sql_calc_found_rows * from product where categoryid=? ORDER BY ProductId desc limit?,?';ExecutePagesql using@categoryId,@startRow,@pageSize;deallocate PreparePagesql;Set_totalreccount=found_rows ();EndThis is just a list of some of the specific differences that I think are often encountered in writing a stored procedure. 

Some common syntax differences between T-SQL and MySQL