MYSQL中插入某几位固定的随机字符串，望高手给于解答

icecatbing

具体要求是在MYSQL表A中插入

前6位固定为123456的16位随机字符串
随机字符串包括字母a-z、A-Z、0-9
其中表A包含字段X、Y、Z

请问SQL语句应该怎么写，请高手帮帮忙十分感谢。

[ 本帖最后由 icecatbing 于 2008-9-1 23:39 编辑 ]

icecatbing

没人么...好几天了

andrefun

你在程序里面生成随机字符窜不行吗?

cloudice

有道理，如果是用程序连接和使用mysql的话
否则，要直接使用的话，还等高手赐教了~~

duoshan

select concat(
char(round((rand())*25)+97),
char(round((rand())*25)+65),
char(round((rand())*25)+65),
char(round((rand())*25)+97),
char(round((rand())*25)+65),
char(round((rand())*25)+65),
char(round((rand())*25)+97),
char(round((rand())*25)+65)
)

---------

+97就是产生小写字母a-z
+65就是产生大写字母A-Z

结果不包含其他字符

duoshan

select concat(
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end,
case when round((rand())*25) > 12 then char(round((rand())*25)+97) else char(round((rand())*25)+65) end
) as keka

也可用 case when 再混淆大小写。

duoshan

select CONCAT('123456',
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end,
case round((rand())*2) when 0 then round((rand())*9)  when 1 then char(round((rand())*25)+97)  else char(round((rand())*25)+65) end
)

-----------
自己改成insert 就可以了:wink:

yueliangdao0608

select concat('123456',left(replace(uuid(),'-',''),10)) as rand_str;