MySQL 通过SUBSTRING_INDEX函数拆分字符串为数组单元

落日映苍穹つ 2024-04-18 00:09 23阅读 0赞

vim test.sh  
\#Define variables  
MYSQL\_HOME=/usr  
SQL\_SCRIPT\_DB\_CONNECTION\_STRING='-uroot -p'root''  
SQL\_SCRIPT\_PARAMETERS\_LIST="'PARAM1 PARAM2 PARAM3'"  
  
\#Execute the command  
\#CMD\_SQL\_SCRIPT\_EXECUTION=$\{MYSQL\_HOME\}/bin/mysql  $\{SQL\_SCRIPT\_DB\_CONNECTION\_STRING\} -e "set  @VAR1=$SQL\_SCRIPT\_PARAMETERS\_LIST ; source $\{SQL\_SCRIPT\_FILE\_LOCATION\}/$\{SQL\_SCRIPT\_NAME\}.sql ;" >> $\{myLogFile\}  
  
$\{MYSQL\_HOME\}/bin/mysql  $\{SQL\_SCRIPT\_DB\_CONNECTION\_STRING\} -e "set  @VAR=$SQL\_SCRIPT\_PARAMETERS\_LIST;   
set @VAR\_NUM=length(@VAR)-length(REPLACE(@VAR,' ',''))+1; /\* define the total number of the variables  \*/   
set @VAR\_POS=0; /\* define the variable position in the string \*/  
/\* define the variables' value, if the the variable doesn't exist, its value is null  \*/  
set @VAR\_1 = if(@VAR\_POS < @VAR\_NUM,SUBSTRING\_INDEX(SUBSTRING\_INDEX(@VAR, ' ', @VAR\_POS:=@VAR\_POS+1),' ',-1),null);  
set @VAR\_2 = if(@VAR\_POS < @VAR\_NUM,SUBSTRING\_INDEX(SUBSTRING\_INDEX(@VAR, ' ', @VAR\_POS:=@VAR\_POS+1),' ',-1),null);  
set @VAR\_3 = if(@VAR\_POS < @VAR\_NUM,SUBSTRING\_INDEX(SUBSTRING\_INDEX(@VAR, ' ', @VAR\_POS:=@VAR\_POS+1),' ',-1),null);  
set @VAR\_4 = if(@VAR\_POS < @VAR\_NUM,SUBSTRING\_INDEX(SUBSTRING\_INDEX(@VAR, ' ', @VAR\_POS:=@VAR\_POS+1),' ',-1),null);  
set @VAR\_5 = if(@VAR\_POS < @VAR\_NUM,SUBSTRING\_INDEX(SUBSTRING\_INDEX(@VAR, ' ', @VAR\_POS:=@VAR\_POS+1),' ',-1),null);  
set @VAR\_6 = if(@VAR\_POS < @VAR\_NUM,SUBSTRING\_INDEX(SUBSTRING\_INDEX(@VAR, ' ', @VAR\_POS:=@VAR\_POS+1),' ',-1),null);  
set @VAR\_7 = if(@VAR\_POS < @VAR\_NUM,SUBSTRING\_INDEX(SUBSTRING\_INDEX(@VAR, ' ', @VAR\_POS:=@VAR\_POS+1),' ',-1),null);  
select @VAR\_1, @VAR\_2, @VAR\_3, @VAR\_4, @VAR\_5, @VAR\_6, @VAR\_7;"  
  
sh test.sh   
Warning: Using a password on the command line interface can be insecure.  
\+--------+--------+--------+--------+--------+--------+--------+  
| @VAR\_1 | @VAR\_2 | @VAR\_3 | @VAR\_4 | @VAR\_5 | @VAR\_6 | @VAR\_7 |  
\+--------+--------+--------+--------+--------+--------+--------+  
| PARAM1 | PARAM2 | PARAM3 | NULL   | NULL   | NULL   | NULL   |  
\+--------+--------+--------+--------+--------+--------+--------+

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2125937/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2125937/