MySQL 通过SUBSTRING_INDEX函数拆分字符串为数组单元-白红宇

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

阅读量：2497 次

发布时间：2019-05-11

本文共 2155 字，大约阅读时间需要 7 分钟。

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 |

+--------+--------+--------+--------+--------+--------+--------+

+--------+--------+--------+--------+--------+--------+--------+

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

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

你可能感兴趣的文章