用SQL随机生成车牌号（排除O和I）

第一步我们先做出可以生成大写字母和数字的随机数。

大写字母：

SELECT char(65+ceiling(rand()*25))

数字：

SELECT ceiling(rand()*9)

然后排除O和I，也就是说排除，char里面的73 和79.

我们用了绝对值函数：abs() ,如下：

65到73的字母：

SELECT char(65+abs(ceiling(rand()*9)-2))  

73到79的字母：

SELECT char(73+abs(ceiling(rand()*9)-4))

79到90的字母：

SELECT char(79+ceiling(rand()*11))

然后过滤O和I的sql：用到了 CASE   WHEN

(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		WHEN
			(65+ceiling(rand()*25)) > 79
			THEN char(79+ceiling(rand()*11))
		ELSE
			ceiling(rand()*9)
		END))

然后我们在来生成随机车牌号：

用到了拼接函数：concat(a,b,c) 结果：abc

(select concat((select(
	CASE
		WHEN
			 ceiling(rand()*25) % 2 = 0
			THEN ceiling(rand()*9)
			ELSE
(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		ELSE
			char(79+ceiling(rand()*11))
		END))
			END)),(select(
	CASE
		WHEN
			 ceiling(rand()*25) % 2 = 0
			THEN ceiling(rand()*9)
			ELSE
(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		ELSE
			char(79+ceiling(rand()*11))
		END))
			END)),(select(
	CASE
		WHEN
			 ceiling(rand()*25) % 2 = 0
			THEN ceiling(rand()*9)
			ELSE
(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		ELSE
			char(79+ceiling(rand()*11))
		END))
			END)),(select(
	CASE
		WHEN
			 ceiling(rand()*25) % 2 = 0
			THEN ceiling(rand()*9)
			ELSE
(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		ELSE
			char(79+ceiling(rand()*11))
		END))
			END)),(select(
	CASE
		WHEN
			 ceiling(rand()*25) % 2 = 0
			THEN ceiling(rand()*9)
			ELSE
(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		ELSE
			char(79+ceiling(rand()*11))
		END))
			END))))

结果如下：

我们还可以变成一下样式：

(select concat((select(
	CASE
		WHEN
			 ceiling(rand()*25) % 2 = 0
			THEN ceiling(rand()*9)
			ELSE
(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		ELSE
			char(79+ceiling(rand()*11))
		END))
			END)),(select(
	CASE
		WHEN
			 ceiling(rand()*25) % 2 = 0
			THEN ceiling(rand()*9)
			ELSE
(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		ELSE
			char(79+ceiling(rand()*11))
		END))
			END)),'**',(select(
	CASE
		WHEN
			 ceiling(rand()*25) % 2 = 0
			THEN ceiling(rand()*9)
			ELSE
(select(
 CASE
		WHEN
			(65+ceiling(rand()*25)) < 73
			THEN char(65+abs(ceiling(rand()*9)-2))
		WHEN
			(65+ceiling(rand()*25)) > 73 AND (65+ceiling(rand()*25)) < 79
			THEN char(73+abs(ceiling(rand()*9)-4))
		ELSE
			char(79+ceiling(rand()*11))
		END))
			END))))

结果如下：