对我来说这是一个相当困难的话题,因为SQL不是我的强项;)
我必须将随机十六进制颜色插入到数据库行中。我该怎么做?是否可能创建一个能够产生数字的函数?
SET [Color] = '#' + CONVERT(VARCHAR(max), CRYPT_GEN_RANDOM(3), 2)
下面是用于MySQL的逻辑,封装在一个函数内进行说明。非常易于使用。
mysql> select random_color();
+----------------+
| random_color() |
+----------------+
| #8F50B4 |
+----------------+
1 row in set (0.00 sec)
它可以被多次调用,每次调用时它都会有不同的颜色。
CREATE FUNCTION `random_color`() RETURNS char(7) CHARSET latin1 DETERMINISTIC
BEGIN
DECLARE str CHAR(7);
SET str = concat('#',SUBSTRING((lpad(hex(round(rand() * 10000000)),6,0)),-6));
RETURN str;
END;
这将在MySQL中为您提供六位十六进制数字代码
SELECT concat('#',SUBSTRING((lpad(hex(round(rand() * 10000000)),6,0)),-6))
这是一个很棒的东西,它可以给你增量颜色。
SELECT *,
concat('#',SUBSTRING((lpad(hex(@curRow := @curRow + 10),6,0)),-6)) AS color
FROM table
INNER JOIN (SELECT @curRow := 5426175) color_start_point
以下方法适用于MySQL:
mysql> SELECT CONCAT('#',LPAD(CONV(ROUND(RAND()*16777215),10,16),6,0)) AS color;
+---------+
| color |
+---------+
| #0E74A9 |
+---------+
1 row in set (0.00 sec)
简要说明:
RAND()
并ROUND()
后将得到RGB颜色范围内的随机无符号整数:(0, 224-1)LPAD()
进行左侧填零CONCAT()
可获取'#'
字符with cte1 as (
select round(round(rand(),1)*15,0) as hex1,
round(round(rand(),1)*15,0) as hex2,
round(round(rand(),1)*15,0) as hex3,
round(round(rand(),1)*15,0) as hex4,
round(round(rand(),1)*15,0) as hex5,
round(round(rand(),1)*15,0) as hex6
),
cte2 as (
select case when hex1 = 10 then 'A'
when hex1 = 11 then 'B'
when hex1 = 12 then 'C'
when hex1 = 13 then 'D'
when hex1 = 14 then 'E'
when hex1 = 15 then 'F'
else str(hex1) end as hex1h,
case when hex2 = 10 then 'A'
when hex2 = 11 then 'B'
when hex2 = 12 then 'C'
when hex2 = 13 then 'D'
when hex2 = 14 then 'E'
when hex2 = 15 then 'F'
else str(hex2) end as hex2h,
case when hex3 = 10 then 'A'
when hex3 = 11 then 'B'
when hex3 = 12 then 'C'
when hex3 = 13 then 'D'
when hex3 = 14 then 'E'
when hex3 = 15 then 'F'
else str(hex3) end as hex3h,
case when hex4 = 10 then 'A'
when hex4 = 11 then 'B'
when hex4 = 12 then 'C'
when hex4 = 13 then 'D'
when hex4 = 14 then 'E'
when hex4 = 15 then 'F'
else str(hex4) end as hex4h,
case when hex5 = 10 then 'A'
when hex5 = 11 then 'B'
when hex5 = 12 then 'C'
when hex5 = 13 then 'D'
when hex5 = 14 then 'E'
when hex5 = 15 then 'F'
else str(hex5) end as hex5h,
case when hex6 = 10 then 'A'
when hex6 = 11 then 'B'
when hex6 = 12 then 'C'
when hex6 = 13 then 'D'
when hex6 = 14 then 'E'
when hex6 = 15 then 'F'
else str(hex6) end as hex6h from cte1)
select '#'+ltrim(hex1h)+ltrim(hex2h)+ltrim(hex3h)+ltrim(hex4h)+ltrim(hex5h)+ltrim(hex6h) from cte2
CREATE FUNCTION dbo.Get_Random_Colour (@intStyle tinyint = 0)
RETURNS varchar(7)
/*
* Purpose: Returns the HTML colour code for a random colour
* Inputs: @intStyle - 0: does not filter the colour range
* 1: avoid dark colours
* 2: avoid light colours
*/
AS
BEGIN
DECLARE @c1 char(2), @c2 char(2), @c3 char(2)
DECLARE @i1 tinyint, @i2 tinyint, @i3 tinyint
DECLARE @strResult As varchar(255)
DECLARE @intLow tinyint = 0
DECLARE @intHigh tinyint = 255
IF @intStyle = 1
SET @intLow = 80
IF @intStyle = 2
SET @intHigh = 140
--Generate random numbers
SELECT @i1 = CAST(ROUND((@intHigh-@intLow) * RandNumber + @intLow,0) as int) from dbo.vRandNumber
SELECT @i2 = CAST(ROUND((@intHigh-@intLow) * RandNumber + @intLow,0) as int) from dbo.vRandNumber
SELECT @i3 = CAST(ROUND((@intHigh-@intLow) * RandNumber + @intLow,0) as int) from dbo.vRandNumber
--Convert them to hex format
SELECT @c1 = FORMAT(@i1, 'X')
SELECT @c2 = FORMAT(@i2, 'X')
SELECT @c3 = FORMAT(@i3, 'X')
--Pad them to two characters
SELECT @strResult = '#'
+ REPLICATE('0', 2-LEN(@c1)) + @c1
+ REPLICATE('0', 2-LEN(@c2)) + @c2
+ REPLICATE('0', 2-LEN(@c3)) + @c3
RETURN @strResult
END
GO
以下是该函数所依赖的视图:
CREATE VIEW [dbo].[vRandNumber]
AS
SELECT RAND() as RandNumber
SELECT CAST('foo' AS VARBINARY)
-- Returns : 0x666F6F
SELECT CAST(0x666F6F AS VARCHAR)
-- Returns : foo
#BA55D3
对应于rgb(186,85,211)
。它的实际十六进制值为:
SELECT
CAST(0xBA AS INT)
-- Returns : 186
VARBINARY
,以便于将其转换回varchar并将输出格式化为十六进制颜色值。CREATE FUNCTION RGBToHex
(
@R INT,
@G INT,
@B INT
)
RETURNS VARCHAR(7)
AS
BEGIN
DECLARE
@VarR VARBINARY,
@VarG VARBINARY,
@VarB VARBINARY,
@Result VARCHAR(7)
SELECT
@VarR = CONVERT(VARBINARY, @R) * 1 ,
@VarG = CONVERT(VARBINARY, @G) * 1 ,
@VarB = CONVERT(VARBINARY, @B) * 1
SET @Result = '#' + SUBSTRING(Convert(VARCHAR(MAX),@VarR, 1), 3, 2) + SUBSTRING(Convert(VARCHAR(MAX),@VarG, 1), 3, 2) + SUBSTRING(Convert(VARCHAR(MAX),@VarB, 1), 3, 2)
RETURN @Result
END
SELECT
RGBToHex(186,85,211)
-- Returns : #BA55D3
;WITH CTE AS (
SELECT
ABS(CHECKSUM(NewId())) % 256 AS R ,
ABS(CHECKSUM(NewId())) % 256 AS G ,
ABS(CHECKSUM(NewId())) % 256 AS B
)
SELECT
dbo.RGBToHex(R,G,B)
FROM CTE
CREATE FUNCTION HexToRGB
(
@Hex VARCHAR(7)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE
@VarH VARCHAR(6),
@R INT,
@G INT,
@B INT,
@Result VARCHAR(100)
SET @VarH = (CASE WHEN LEFT(@Hex, 1) = '#' THEN SUBSTRING(@Hex, 2,6) ELSE @Hex END)
SELECT
@R = CONVERT(INT, CONVERT(VARBINARY, '0x' + SUBSTRING(@VarH, 1,2), 1) ),
@G = CONVERT(INT, CONVERT(VARBINARY, '0x' + SUBSTRING(@VarH, 3,2), 1) ),
@B = CONVERT(INT, CONVERT(VARBINARY, '0x' + SUBSTRING(@VarH, 5,2), 1) )
SET @Result = 'rgb(' + CAST(@R AS VARCHAR(3) ) + ',' + CAST(@G AS VARCHAR(3) ) + ',' + CAST(@B AS VARCHAR(3) ) + ')'
RETURN @Result
END
select concat("#", conv(round(rand() * 255), 10, 16), conv(round(rand() * 255), 10, 16), conv(round(rand() * 255), 10, 16));
SELECT concat('#',lpad(to_hex(round(random() * 10000000)::int4),6,'0'))
我使用以下代码将随机颜色设置为表格的Color
字段:
UPDATE "MyTable" SET "Color" = concat('#',lpad(to_hex(round(random() * 10000000)::int8),6,'0'));
看来你需要使用 rand 函数 - https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand - 例如:
INSERT INTO ... VALUES ( ... , ROUND(RAND(255 * 255 * 255)), ...)
HEX
来进行转换? - Ed HealSELECT section
, ifNull(concat('#'
, format('%X', mod(section, 256))
, format('%X', mod(div(section, 256), 256))
, format('%X', mod(div(div(section, 256), 256), 256))
), '#000000' #black=default
) as html_color
FROM StopTimes;
SELECT section, ifNull(concat('#'
, format('%X', mod(abs(farm_fingerprint(section)), 256))
, format('%X', mod(abs(div(farm_fingerprint(section), 256)), 256))
, format('%X', mod(abs(div(div(farm_fingerprint(section), 256), 256)), 256))
), '#000000' #black=default
) as html_color
FROM StopTimes;