MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.23


CREATE TABLE test
(
  ip_v6 VARCHAR (39) NOT NULL, 
  host VARCHAR (100) NOT NULL,


  ip_v6_token_1 VARCHAR(39) GENERATED ALWAYS AS 
    (SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 1), ':', -1)) STORED,
  ip_v6_token_2 VARCHAR(39) GENERATED ALWAYS AS 
    (SUBSTRING_INDEX(SUBSTRING_INDEX(ip_v6, ':', 2), ':', -1)) STORED,

  -- as in simple solution, same for tokens 3-7 as per requirements!

  INDEX (ip_v6_token_1),  -- required for FOREIGN KEYs
  INDEX (ip_v6_token_2),


  CONSTRAINT test_ip_v6_pk PRIMARY KEY (ip_v6),
  CONSTRAINT ip_v6_regexp_ck 
    CHECK(ip_v6 REGEXP '^([[:xdigit:]]{1,4}:){7,7}[[:xdigit:]]{1,4}$')

);
INSERT INTO test (ip_v6, host) 
VALUES 
('2001:0db8:0000:FFF0:0000:8a2e:0370:7334', 'host_1'),
('10:9:8:7:6:6:7:6'                       , 'host_2');
--
-- This is expected to fail because of the CHECK (IS_IPV6(ip_v6)) constraint.
-- It fails on the second element XXXX.
--

INSERT INTO test (ip_v6, host) 
VALUES 
('4001:XXXX:2222:FFF0:0000:8a2e:0370:7334', 'host_3');
Check constraint 'ip_v6_regexp_ck' is violated.

INSERT INTO test (ip_v6, host) 
VALUES 
('4001:1111:2222:FFF0:0000:8a2e:0370:7334', 'host_3');
SELECT 
  ip_v6_token_1 AS "Token 1",
  ip_v6_token_2 AS "Token 2",
  ip_v6         AS "IPv6:",
  host          AS "Host:"
FROM test ORDER BY host;


Token 1
Token 2
IPv6:
Host:


2001
0db8
2001:0db8:0000:FFF0:0000:8a2e:0370:7334
host_1

10
9
10:9:8:7:6:6:7:6
host_2

4001
1111
4001:1111:2222:FFF0:0000:8a2e:0370:7334
host_3


CREATE TABLE test_fk
(
  t_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  token_1 VARCHAR (4) NOT NULL,
  token_2 VARCHAR (4),
  
  --
  -- Other fields
  --
  
  CONSTRAINT test_fk_test_ip_v6_token_1_fk 
    FOREIGN KEY (token_1) 
      REFERENCES test (ip_v6_token_1) ON DELETE CASCADE ON UPDATE CASCADE,
  
  CONSTRAINT test_fk_test_ip_v6_token_2_fk 
    FOREIGN KEY (token_2) 
      REFERENCES test (ip_v6_token_2) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO test_fk (token_1, token_2)
VALUES 
('2001', '9'), ('2001', '9'), ('2001', '1111'),
('10', '1111'), ('4001', '0db8'), ('2001', '9'); 
SELECT * FROM test_fk;


t_id
token_1
token_2


1
2001
9

2
2001
9

3
2001
1111

4
10
1111

5
4001
0db8

6
2001
9


DELETE FROM test WHERE host = 'host_3';
SELECT * FROM test_fk;


t_id
token_1
token_2


1
2001
9

2
2001
9

6
2001
9


--
-- This time we expect a valid IPv6 address to fail because of the consecutive colon
-- (:) characters - this is valid for IPv6, but not for the regexp CHECK constraint
--

UPDATE test SET ip_v6 = '123:123::123' WHERE ip_v6 = '10:9:8:7:6:6:7:6';
Check constraint 'ip_v6_regexp_ck' is violated.

--
-- This passes, but is a pain to type!
--

UPDATE test SET ip_v6 = '123:123:123:123:123:123:123:123' 
  WHERE ip_v6 = '10:9:8:7:6:6:7:6';
SELECT * FROM test_fk;


t_id
token_1
token_2


1
2001
123

2
2001
123

6
2001
123


SELECT * FROM test;


ip_v6
host
ip_v6_token_1
ip_v6_token_2


123:123:123:123:123:123:123:123
host_2
123
123

2001:0db8:0000:FFF0:0000:8a2e:0370:7334
host_1
2001
0db8


INSERT INTO test_fk (token_1, token_2) VALUES ('2001', '0db8');
SELECT * FROM test_fk;


t_id
token_1
token_2


1
2001
123

2
2001
123

6
2001
123

7
2001
0db8


--
-- Fails as expected - there is no value 5432 in any corresponding place in the 
-- ip_v6 field in the test table.
--

INSERT INTO test_fk (token_1, token_2) VALUES ('2001', '5432');
Cannot add or update a child row: a foreign key constraint fails (`db_2004644084`.`test_fk`, CONSTRAINT `test_fk_test_ip_v6_token_2_fk` FOREIGN KEY (`token_2`) REFERENCES `test` (`ip_v6_token_2`) ON DELETE CASCADE ON UPDATE CASCADE)

--
-- 
--
--
-- Regular expression tests! - many of these fail for MySQL when they shouldn't!
--
--
-- should work but doesn't for MySQL 
-- \d can be replaced by [[:hexdigit:]] - but this is non-standard!
--
-- BUT!!! FAR, FAR worse than failing is returning incorrect results!!!!
--

SELECT REGEXP_REPLACE(ip_v6, '^\d{1,4}', 'YYYYY') FROM test;  


REGEXP_REPLACE(ip_v6, '^\d{1,4}', 'YYYYY')


123:123:123:123:123:123:123:123

2001:0db8:0000:FFF0:0000:8a2e:0370:7334


--
-- All the same for MySQL - see the REGEXP_REPLACE documentation.
--

SELECT 
  REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256'),
  REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256', 1),
  REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256', 1, 0)
FROM test;  


REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256')
REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256', 1)
REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256', 1, 0)


256:256:256:256:256:256:256:256
256:256:256:256:256:256:256:256
256:256:256:256:256:256:256:256

256:256:256:256:256:256:256:256
256:256:256:256:256:256:256:256
256:256:256:256:256:256:256:256


SELECT REGEXP_REPLACE(ip_v6, '[[:xdigit:]]', '') FROM test;


REGEXP_REPLACE(ip_v6, '[[:xdigit:]]', '')


:::::::

:::::::


--
-- Just checking that REGEXP_REPLACE was working as it's supposed to do! Here, I 
-- replace all hex digits with nothing, leaving just the colon in the string!
--

SELECT REGEXP_REPLACE('2001:1001', '[[:xdigit:]]', '') AS "RR_test";


RR_test


:


--
-- Should work but fails for MySQL! Worse than simply failing, it returns some sort
-- of result which appears to be just the string literals '3', '2' & '1' - potentially
-- the source of very difficult to trace errors.
--
--
-- BUT!!! FAR, FAR worse than failing is returning incorrect results!!!!
--

SELECT 
  REGEXP_REPLACE(ip_v6, '(^[[:xdigit:]]{1,4}):(.*$)', '\1') 
  AS "Rep text 1",
  REGEXP_REPLACE(ip_v6, '(^[[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):(.*$)', '\3   \2   \1') 
  AS "Rep text",
  ip_v6
FROM test;


Rep text 1
Rep text
ip_v6


1
3   2   1
123:123:123:123:123:123:123:123

1
3   2   1
2001:0db8:0000:FFF0:0000:8a2e:0370:7334


--
-- Should work but fails for MySQL! Worse than simply failing, it returns some sort
-- of result which appears to be just the string literals '3', '2' & '1' - potentially
-- the source of very difficult to trace errors.
--

SELECT 
REGEXP_REPLACE(ip_v6, '(^[[:xdigit:]]{1,4}:)([[:xdigit:]]{1,4}:)(.*$)', '\3   \2   \1') 
AS "Rep text"
FROM test;


Rep text


3   2   1

3   2   1


--
-- Should work but fails for MySQL! Worse than simply failing, it returns some sort
-- of result which appears to be just the string literals '3', '2' & '1' - potentially
-- the source of very difficult to trace errors.
--
--
-- BUT!!! FAR, FAR worse than failing is returning incorrect results!!!!
--

SELECT
  REGEXP_REPLACE
  (
    ip_v6, '(^[[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):(.*$)', '\4 xx \3   \2   \1'
  ) 
  AS "Rep text 2",
  ip_v6
FROM test;


Rep text 2
ip_v6


4 xx 3   2   1
123:123:123:123:123:123:123:123

4 xx 3   2   1
2001:0db8:0000:FFF0:0000:8a2e:0370:7334

Token 1	Token 2	IPv6:	Host:
2001	0db8	2001:0db8:0000:FFF0:0000:8a2e:0370:7334	host_1
10	9	10:9:8:7:6:6:7:6	host_2
4001	1111	4001:1111:2222:FFF0:0000:8a2e:0370:7334	host_3

ip_v6	host	ip_v6_token_1	ip_v6_token_2
123:123:123:123:123:123:123:123	host_2	123	123
2001:0db8:0000:FFF0:0000:8a2e:0370:7334	host_1	2001	0db8

REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256')	REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256', 1)	REGEXP_REPLACE(ip_v6, '[[:xdigit:]]{1,4}', '256', 1, 0)
256:256:256:256:256:256:256:256	256:256:256:256:256:256:256:256	256:256:256:256:256:256:256:256
256:256:256:256:256:256:256:256	256:256:256:256:256:256:256:256	256:256:256:256:256:256:256:256

Rep text 1	Rep text	ip_v6
1	3 2 1	123:123:123:123:123:123:123:123
1	3 2 1	2001:0db8:0000:FFF0:0000:8a2e:0370:7334

Rep text 2	ip_v6
4 xx 3 2 1	123:123:123:123:123:123:123:123
4 xx 3 2 1	2001:0db8:0000:FFF0:0000:8a2e:0370:7334