SQL Server
由于工作的原因：上家公司的数据库全采用MySQL，所以不得不用它。因此也学到了MySQL的一些知识，但考虑到今后可能没机会使用了，所以想趁现在离职在家休息，打算把这些东西整理一下，也为了万一今后能用上，留个参考的资源。考虑到一直在使用SQL Server，所以就打算直接与SQL Server对比来写。
本文将主要列出MySQL与SQL Server不同的地方，且以常用的存储过程的相关内容为主。
1. 标识符限定符
| 数据库 | 标识符限定符 |
| SQL Server | [] |
| MySQL | `` |
2. 字符串相加
| 数据库 | 字符串相加 |
| SQL Server | 直接用 + |
| MySQL | concat() |
3. isnull()
| 数据库 | isnull() |
| SQL Server | isnull() |
| MySQL | ifnull() |
注意：MySQL也有isnull()函数，但意义不一样
4. getdate()
| 数据库 | getdate() |
| SQL Server | getdate() |
| MySQL | now() |
5. newid()
| 数据库 | newid() |
| SQL Server | newid() |
| MySQL | uuid() |
6. @@ROWCOUNT
| 数据库 | @@ROWCOUNT |
| SQL Server | @@ROWCOUNT |
| MySQL | row_count() |
注意：MySQL的这个函数仅对于update, insert, delete有效
7. SCOPE_IDENTITY()
| 数据库 | SCOPE_IDENTITY() |
| SQL Server | SCOPE_IDENTITY() |
| MySQL | last_insert_id() |
8. if ... else ...
| 数据库 | if ... else ... |
| SQL Server |
<ol class="dp-sql">
<li class="alt"><span><span> IF Boolean_expression </span></span></li>
<li><span> { sql_statement | statement_block } </span></li>
<li class="alt">
<span>[ </span><span class="keyword"><strong><font color="#006699">ELSE</font></strong></span><span> </span>
</li>
<li><span> { sql_statement | statement_block } ] </span></li>
</ol>登录后复制 |
| MySQL |
<ol class="dp-sql"> <li class="alt"><span><span>IF search_condition </span><span class="keyword"><strong><font color="#006699">THEN</font></strong></span><span> statement_list </span></span></li> <li> <span> [ELSEIF search_condition </span><span class="keyword"><strong><font color="#006699">THEN</font></strong></span><span> statement_list] ... </span> </li> <li class="alt"> <span> [</span><span class="keyword"><strong><font color="#006699">ELSE</font></strong></span><span> statement_list] </span> </li> <li> <span class="keyword"><strong><font color="#006699">END</font></strong></span><span> IF </span> </li> </ol> 登录后复制 注意：对于MySql来说，then, end if是必须的。类似的还有其它的流程控制语句，这里就不一一列出。 |
9. declare
其实，SQL Server和MySQL都有这个语句，用于定义变量，但差别在于：在MySQL中，DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。这个要求在写游标时，会感觉很BT.
10. 游标的写法
SQL Server
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> @tempShoppingCart </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span> (ProductId </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, Quantity </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>) </span></span></li> <li> <span class="keyword"><strong><font color="#006699">insert</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">into</font></strong></span><span> @tempShoppingCart (ProductId, Quantity) </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> ProductId, Quantity </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> ShoppingCart </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> UserGuid = @UserGuid </span> </li> <li><span> </span></li> <li class="alt"><span> </span></li> <li> <span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> @productId </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> @quantity </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span> </li> <li> <span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> tempCartCursor </span><span class="keyword"><strong><font color="#006699">cursor</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">for</font></strong></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> ProductId, Quantity </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> @tempShoppingCart </span> </li> <li><span> </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">open</font></strong></span><span> tempCartCursor </span> </li> <li> <span class="keyword"><strong><font color="#006699">fetch</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">next</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> tempCartCursor </span><span class="keyword"><strong><font color="#006699">into</font></strong></span><span> @productId, @quantity </span> </li> <li class="alt"><span>while @@FETCH_STATUS = 0 </span></li> <li> <span class="keyword"><strong><font color="#006699">begin</font></strong></span><span> </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">update</font></strong></span><span> Product </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> SellCount = SellCount + @quantity </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> productId = @productId </span> </li> <li><span> </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">fetch</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">next</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> tempCartCursor </span><span class="keyword"><strong><font color="#006699">into</font></strong></span><span> @productId, @quantity </span> </li> <li> <span class="keyword"><strong><font color="#006699">end</font></strong></span><span> </span> </li> <li class="alt"><span> </span></li> <li> <span class="keyword"><strong><font color="#006699">close</font></strong></span><span> tempCartCursor </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">deallocate</font></strong></span><span> tempCartCursor </span> </li> </ol>
MySQL
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> m_done </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">default</font></strong></span><span> 0; </span></span></li> <li> <span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> m_sectionId </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>; </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> m_newsId </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>; </span> </li> <li><span> </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> _cursor_SN </span><span class="keyword"><strong><font color="#006699">cursor</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">for</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> sectionid, newsid </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> _temp_SN; </span> </li> <li> <span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">continue</font></strong></span><span> handler </span><span class="keyword"><strong><font color="#006699">for</font></strong></span><span> </span><span class="op"><font color="#808080">not</font></span><span> found </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> m_done = 1; </span> </li> <li class="alt"><span> </span></li> <li> <span class="keyword"><strong><font color="#006699">create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">temporary</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span> _temp_SN </span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> sectionid, newsid </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> SectionNews </span><span class="keyword"><strong><font color="#006699">group</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">by</font></strong></span><span> sectionid, newsid </span><span class="keyword"><strong><font color="#006699">having</font></strong></span><span> </span><span class="func"><font color="#ff1493">count</font></span><span>(*) > 1; </span> </li> <li class="alt"><span> </span></li> <li> <span class="keyword"><strong><font color="#006699">open</font></strong></span><span> _cursor_SN; </span> </li> <li class="alt"><span>while( m_done = 0 ) do </span></li> <li> <span> </span><span class="keyword"><strong><font color="#006699">fetch</font></strong></span><span> _cursor_SN </span><span class="keyword"><strong><font color="#006699">into</font></strong></span><span> m_sectionId, m_newsId; </span> </li> <li class="alt"><span> </span></li> <li> <span> if( m_done = 0 ) </span><span class="keyword"><strong><font color="#006699">then</font></strong></span><span> </span> </li> <li class="alt"> <span> </span><span class="comment"><font color="#008200">-- 具体的处理逻辑 </font></span><span> </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">end</font></strong></span><span> if; </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">end</font></strong></span><span> while; </span> </li> <li> <span class="keyword"><strong><font color="#006699">close</font></strong></span><span> _cursor_SN; </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">drop</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span> _temp_SN; </span> </li> </ol>
注意：为了提高性能，通常在表变量上打开游标，不要直接在数据表上打开游标。
11. 分页的处理
SQL Server
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">procedure</font></strong></span><span> GetProductByCategoryId( </span></span></li> <li> <span> @CategoryID </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, </span> </li> <li class="alt"> <span> @PageIndex </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> = 0, </span> </li> <li> <span> @PageSize </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> = 20, </span> </li> <li class="alt"> <span> @TotalRecords </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">output</font></strong></span><span> </span> </li> <li><span>) </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">as</font></strong></span><span> </span> </li> <li> <span class="keyword"><strong><font color="#006699">begin</font></strong></span><span> </span> </li> <li class="alt"><span> </span></li> <li> <span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> @ResultTable </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span> </span> </li> <li class="alt"><span>( </span></li> <li> <span> RowIndex </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, </span> </li> <li class="alt"> <span> ProductID </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, </span> </li> <li><span> ProductName nvarchar(50), </span></li> <li class="alt"> <span> CategoryID </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, </span> </li> <li><span> Unit nvarchar(10), </span></li> <li class="alt"><span> UnitPrice money, </span></li> <li> <span> Quantity </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span> </li> <li class="alt"><span>); </span></li> <li><span> </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">insert</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">into</font></strong></span><span> @ResultTable </span> </li> <li> <span class="keyword"><strong><font color="#006699">select</font></strong></span><span> row_number() over (</span><span class="keyword"><strong><font color="#006699">order</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">by</font></strong></span><span> ProductID </span><span class="keyword"><strong><font color="#006699">asc</font></strong></span><span>) </span><span class="keyword"><strong><font color="#006699">as</font></strong></span><span> RowIndex, </span> </li> <li class="alt"><span> p.ProductID, p.ProductName, p.CategoryID, p.Unit, p.UnitPrice, p.Quantity </span></li> <li> <span class="keyword"><strong><font color="#006699">from</font></strong></span><span> Products </span><span class="keyword"><strong><font color="#006699">as</font></strong></span><span> p </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">where</font></strong></span><span> CategoryID = @CategoryID; </span> </li> <li><span> </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">select</font></strong></span><span> @TotalRecords = </span><span class="func"><font color="#ff1493">count</font></span><span>(*) </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> @ResultTable; </span> </li> <li><span> </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">select</font></strong></span><span> * </span> </li> <li> <span class="keyword"><strong><font color="#006699">from</font></strong></span><span> @ResultTable </span> </li> <li class="alt"> <span class="keyword"><strong><font color="#006699">where</font></strong></span><span> RowIndex > (@PageSize * @PageIndex) </span><span class="op"><font color="#808080">and</font></span><span> RowIndex </span> </li> <li><span> </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">end</font></strong></span><span>; </span> </li> </ol>
当然，SQL Server中并不只有这一种写法，只是这种写法是比较常见而已。
MySQL
<ol class="dp-sql"> <li class="alt"><span><span class="keyword"><strong><font color="#006699">create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">procedure</font></strong></span><span> GetProductsByCategoryId( </span></span></li> <li> <span> </span><span class="op"><font color="#808080">in</font></span><span> _categoryId </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, </span> </li> <li class="alt"> <span> </span><span class="op"><font color="#808080">in</font></span><span> _pageIndex </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, </span> </li> <li> <span> </span><span class="op"><font color="#808080">in</font></span><span> _pageSize </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span>, </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">out</font></strong></span><span> _totalRecCount </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span> </li> <li><span>) </span></li> <li class="alt"> <span class="keyword"><strong><font color="#006699">begin</font></strong></span><span> </span> </li> <li><span> </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> @categoryId = _categoryId; </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> @startRow = _pageIndex * _pageSize; </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> @pageSize = _pageSize; </span> </li> <li><span> </span></li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">prepare</font></strong></span><span> PageSql </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> </span><span class="string"><font color="#0000ff">'select sql_calc_found_rows * from product where categoryId = ? order by ProductId desc limit ?, ?'</font></span><span>; </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">execute</font></strong></span><span> PageSql using @categoryId, @startRow, @pageSize; </span> </li> <li class="alt"> <span> </span><span class="keyword"><strong><font color="#006699">deallocate</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">prepare</font></strong></span><span> PageSql; </span> </li> <li> <span> </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> _totalRecCount = found_rows(); </span> </li> <li class="alt"><span> </span></li> <li> <span class="keyword"><strong><font color="#006699">end</font></strong></span><span> </span> </li> </ol>
MySQL与SQL Server的差别实在太多，以上只是列出了本人认为经常在写存储过程中会遇到的一些具体的差别之处。
原文链接：http://www.cnblogs.com/fish-li/archive/2011/04/05/2006107.html
