Seata入门系列(13)-Seata之SQL语句限制

小鱼儿 2023-10-09 13:49 19阅读 0赞

#### 文章目录 ####

*   *  前言
     *  SQL限制
     *   *  DML语句
         *  SQL修饰
         *  函数

### 前言 ###

以下内容摘自[官网][Link 1]

在使用Seata 的时候，对SQL语句还是有很多限制，所以在使用的时候需要格外注意这个问题（较新版本可能已支持）。

### SQL限制 ###

Seata 事务目前支持 INSERT、UPDATE、DELETE 三类 DML 语法的部分功能，这些类型都是已经经过Seata开源社区的验证。SQL 的支持范围还在不断扩大，建议在本文限制的范围内使用。

**使用限制**：

*  不支持 SQL 嵌套
 *  不支持多表复杂 SQL
 *  不支持存储过程、触发器
 *  不支持批量更新 SQL（新版已支持）

#### DML语句 ####

本文介绍 DML 语句类型、SQL 实例以及 Seata 是否支持，帮助您在 Seata 更顺畅的使用 SQL。

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16]

#### SQL修饰 ####

Seata 的隔离级别默认为读未提交，该模式下本文表中的 select 语句的 SQL 修饰用法都是支持的；使用 for update 代理可以让 Seata 达到读已提交，该模式下 select 语句的用法只能部分支持。  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 3]

#### 函数 ####

在本文下面的表列出了 DQL 语句和 DML 语句对函数的支持情况。需要注意的是，在 DML 语句中使用函数，不能将其作为主键。

DML语句支持

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>是否支持</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT(string2[,…])</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>INSTR(string,substring)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LCASE(string2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LEFT(string2,length)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LENGTH(string)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LOAD_FILE(file_name)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LOCATE(substring,string[,start_position])</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LPAD(string2,length,pad)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LTRIM(string2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>REPEAT(string2,count)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>REPLACE(str,search_str,replace_str)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>RPAD(string2,length,pad)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>RTRIM(string2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>STRCMP(string1,string2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING(str,position[,length])</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>TRIM([[BOTH\LEADING\TRAILING][padding]FROM]string2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>UCASE(string2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>RIGHT(string2,length)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SPACE(count)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>ABS(number2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>BIN(decimal_number)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CEILING(number2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CONV(number2,from_base,to_base)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>FLOOR(number2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>FORMAT(number,decimal_places)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>HEX(DecimalNumber)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LEAST(number,number2[,…])</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MOD(numerator,denominator)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>POWER(number,power)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>RAND([seed])</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>ROUND(number[,decimals])</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SIGN(number2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SQRT(number2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>ADDTIME(date2,time_interval)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CONVERT_TZ(datetime2,fromTZ,toTZ)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CURRENT_DATE()</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIME()</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIMESTAMP()</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATE(datetime)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATE_ADD(date2,INTERVALd_valued_type)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATE_FORMAT(datetime,FormatCodes)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATE_SUB(date2,INTERVALd_valued_type)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATEDIFF(date1,date2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAY(date)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAYNAME(date)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAYOFWEEK(date)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAYOFYEAR(date)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>EXTRACT(interval_nameFROMdate)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MAKEDATE(year,day)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MAKETIME(hour,minute,second)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MONTHNAME(date)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>NOW()</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SEC_TO_TIME(seconds)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>STR_TO_DATE(string,format)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>TIMEDIFF(datetime1,datetime2)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>TIME_TO_SEC(time)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>WEEK(date_time[,start_of_week])</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>YEAR(datetime)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAYOFMONTH(datetime</td> 
   <td>) 是</td> 
  </tr> 
  <tr> 
   <td>HOUR(datetime)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LAST_DAY(date)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MICROSECOND(datetime)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MONTH(datetime)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MINUTE(datetime)</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>FIRST()</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>LAST()</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>AVG()</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>SUM()</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

DQL语句支持

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>读未提交</th> 
   <th>读已提交</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT(string2[,…])</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>INSTR(string,substring)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LCASE(string2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LEFT(string2,length)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LENGTH(string)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LOAD_FILE(file_name)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LOCATE(substring,string[,start_position])</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LPAD(string2,length,pad)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LTRIM(string2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>REPEAT(string2,count)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>REPLACE(str,search_str,replace_str)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>RPAD(string2,length,pad)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>RTRIM(string2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>STRCMP(string1,string2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING(str,position[,length])</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>TRIM([[BOTH\LEADING\TRAILING][padding]FROM]string2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>UCASE(string2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>RIGHT(string2,length)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SPACE(count)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>ABS(number2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>BIN(decimal_number)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CEILING(number2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CONV(number2,from_base,to_base)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>FLOOR(number2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>FORMAT(number,decimal_places)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>HEX(DecimalNumber)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LEAST(number,number2[,…])</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MOD(numerator,denominator)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>POWER(number,power)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>RAND([seed])</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>ROUND(number[,decimals])</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SIGN(number2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SQRT(number2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>ADDTIME(date2,time_interval)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CONVERT_TZ(datetime2,fromTZ,toTZ)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CURRENT_DATE()</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIME()</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIMESTAMP()</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATE(datetime)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATE_ADD(date2,INTERVALd_valued_type)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATE_FORMAT(datetime,FormatCodes)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATE_SUB(date2,INTERVALd_valued_type)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DATEDIFF(date1,date2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAY(date)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAYNAME(date)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAYOFWEEK(date)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAYOFYEAR(date)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>EXTRACT(interval_nameFROMdate)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MAKEDATE(year,day)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MAKETIME(hour,minute,second)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MONTHNAME(date)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>NOW()</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>SEC_TO_TIME(seconds)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>STR_TO_DATE(string,format)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>TIMEDIFF(datetime1,datetime2)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>TIME_TO_SEC(time)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>WEEK(date_time[,start_of_week])</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>YEAR(datetime)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>DAYOFMONTH(datetime)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>HOUR(datetime)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>LAST_DAY(date)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MICROSECOND(datetime)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MONTH(datetime)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>MINUTE(datetime)</td> 
   <td>是</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>FIRST()</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>LAST()</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>AVG()</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>SUM()</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td>是</td> 
   <td>否</td> 
  </tr> 
 </tbody> 
</table>

[Link 1]: http://seata.io/zh-cn/docs/user/sqlreference/sql-restrictions.html
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/8236fd49ae3841c1b23aa696a72005fa.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/9d8123d0f30f4485b57e16890f007946.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/d73219a48611424dbc3a9d36e5b3372e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAcXFfNDM0Mzc4NzQ_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/e68f59dd35cd48dfa9226f899a23155f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAcXFfNDM0Mzc4NzQ=,size_20,color_FFFFFF,t_70,g_se,x_16