Some common usage differences between MySql and SqlServer

Due to work reasons: the databases of the previous company all use MySql, so they have to use it. Therefore, I also learned some knowledge about MySql, but considering that I may not be able to use it in the future, I want to take a break at home while I am leaving, and I plan to sort out these things so that I can use them in the future, leave a reference resource. Considering that SQL server has been used, I plan to directly compare it with SQL Server.

This article mainly lists the differences between MySql and SqlServer, and focuses on common stored procedures.

1. identifier qualifier

SqlServer	[]
MySql	''

2. String Addition

SqlServer	Direct use +
MySql	Concat ()

3. isnull ()

SqlServer	Isnull ()
MySql	Ifnull () Note: MySql also has the isnull () function, but it has different meanings.

4. getdate ()

SqlServer	Getdate ()
MySql	Now ()

5. newid ()

SqlServer	Newid ()
MySql	Uuid ()

6. @ ROWCOUNT

SqlServer	@ ROWCOUNT
MySql	Row_count () Note: This MySql function is only valid for update, insert, and delete.

7. SCOPE_IDENTITY ()

SqlServer	SCOPE_IDENTITY ()
MySql	Last_insert_id ()

8. if... else...

SqlServer	IF Boolean_expression {SQL _statement | statement_block} [ELSE {SQL _statement | statement_block}] -- to define statement blocks, use the control flow keyword BEGIN and END.
MySql	IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list]END IF

Note: For MySql, then and end if are required. Similarly, other process control statements are not listed here.

9. declare

In fact, both SqlServer and MySql have this statement to define variables, but the difference is that in MySql, DECLARE is only used for BEGIN... in the END compound statement, and must begin with the compound statement, before any other statement. This requirement is very BT when writing the cursor.

10. cursor writing

SqlServer

declare @tempShoppingCart table (ProductId int, Quantity int)insert into @tempShoppingCart (ProductId, Quantity)select ProductId, Quantity from ShoppingCart where UserGuid = @UserGuiddeclare @productId intdeclare @quantity intdeclare tempCartCursor cursor for select ProductId, Quantity from @tempShoppingCartopen tempCartCursorfetch next from tempCartCursor into @productId, @quantitywhile @@FETCH_STATUS = 0beginupdate Product set SellCount = SellCount + @quantitywhere productId = @productIdfetch next from tempCartCursor into @productId, @quantityendclose tempCartCursordeallocate tempCartCursor

MySql

Declare m_done int default 0; declare m_sectionId int; declare m_newsId int; declare _ cursor_SN cursor for select sesid ID, newsid from _ temp_SN; declare continue handler for not found set m_done = 1; create temporary table _ temp_SN select sectionid, newsid from SectionNews group by sectionid, newsid having count (*)> 1; open _ cursor_SN; while (m_done = 0) dofetch _ cursor_SN into m_sectionId, m_newsId; if (m_done = 0) then -- specific processing logic end if; end while; close _ cursor_SN; drop table _ temp_SN;

Note: to improve performance, the cursor is usually opened on the table variable. Do not open the cursor directly on the data table.

11. Paging Processing

SqlServer

Create procedure GetProductByCategoryId (@ CategoryID int, @ PageIndex int = 0, @ PageSize int = 20, @ TotalRecords int output) asbegin declare @ ResultTable table (RowIndex int, ProductID int, productName nvarchar (50), CategoryID int, Unit nvarchar (10), UnitPrice money, Quantity int); insert into @ ResultTable select row_number () over (order by ProductID asc) as RowIndex, p. productID, p. productName, p. categoryID, p. unit, p. unitPrice, p. quantity from Products as p where CategoryID = @ CategoryID; select @ TotalRecords = count (*) from @ ResultTable; select * from @ ResultTable where RowIndex> (@ PageSize * @ PageIndex) and RowIndex Of course, SQL server does not only use this method, but it is quite common.

MySql

create procedure GetProductsByCategoryId( in _categoryId int, in _pageIndex int, in _pageSize int, out _totalRecCount int)begin set @categoryId = _categoryId; set @startRow = _pageIndex * _pageSize; set @pageSize = _pageSize; prepare PageSql from 'select sql_calc_found_rows * from product where categoryId = ? order by ProductId desc limit ?, ?'; execute PageSql using @categoryId, @startRow, @pageSize; deallocate prepare PageSql; set _totalRecCount = found_rows(); end

There are too many differences between MySql and SqlServer. The above only lists some specific differences that I think will often encounter in the write stored procedure.

Last year, I sorted out some common MySql functions. If necessary, click here to download them.