MySQL 8.0でWITH RECURSIVEを使う

yum -y install mysql-community-client-* mysql-community-common-* mysql-community-libs-* mysql-community-server-* mysql-community-libs-compat-*
systemctl start mysqld
systemctl enable mysqld

cat /var/log/mysqld.log | grep "temporary password"

mysql -u root -p

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

CREATE DATABASE test;
USE test;
CREATE TABLE planes (name VARCHAR(255), departure VARCHAR(255), depart_at TIME, arrival VARCHAR(255), arrive_at TIME);
INSERT INTO planes VALUES
 ('SH01','千歳','08:30','羽田','10:00'),
 ('SH02','千歳','12:30','羽田','14:00'),
 ('SH03','千歳','16:30','羽田','18:00'),
 ('SH04','千歳','21:30','羽田','23:00'),
 ('HS01','羽田','08:30','千歳','10:00'),
 ('HS02','羽田','12:30','千歳','14:00'),
 ('HS03','羽田','16:30','千歳','18:00'),
 ('HS04','羽田','21:30','千歳','23:00'),
 ('SC01','千歳','07:30','中部','09:10'),
 ('SC02','千歳','15:30','中部','17:10'),
 ('SC03','千歳','20:30','中部','22:10'),
 ('CS01','中部','07:30','千歳','09:10'),
 ('CS02','中部','15:30','千歳','17:10'),
 ('CS03','中部','20:30','千歳','22:10'),
 ('SK01','千歳','08:00','関西','09:50'),
 ('SK02','千歳','12:00','関西','13:50'),
 ('SK03','千歳','16:00','関西','17:50'),
 ('SK04','千歳','21:00','関西','22:50'),
 ('KS01','関西','08:00','千歳','09:50'),
 ('KS02','関西','12:00','千歳','13:50'),
 ('KS03','関西','16:00','千歳','17:50'),
 ('KS04','関西','21:00','千歳','22:50'),
 ('SF01','千歳','07:50','福岡','10:10'),
 ('SF02','千歳','11:50','福岡','14:10'),
 ('SF03','千歳','20:50','福岡','23:10'),
 ('FS01','福岡','07:50','千歳','10:10'),
 ('FS02','福岡','11:50','千歳','14:10'),
 ('FS03','福岡','20:50','千歳','23:10'),
 ('HC01','羽田','11:00','中部','11:50'),
 ('HC02','羽田','16:00','中部','16:50'),
 ('CH01','中部','11:00','羽田','11:50'),
 ('CH02','中部','16:00','羽田','16:50'),
 ('HK01','羽田','08:20','関西','09:30'),
 ('HK02','羽田','12:20','関西','13:30'),
 ('HK03','羽田','22:20','関西','23:30'),
 ('KH01','関西','08:20','羽田','09:30'),
 ('KH02','関西','12:20','羽田','13:30'),
 ('KH03','関西','22:20','羽田','23:30'),
 ('HF01','羽田','08:10','福岡','09:40'),
 ('HF02','羽田','13:10','福岡','14:40'),
 ('HF03','羽田','17:10','福岡','18:40'),
 ('HF04','羽田','21:10','福岡','22:40'),
 ('FH01','福岡','08:10','羽田','09:40'),
 ('FH02','福岡','13:10','羽田','14:40'),
 ('FH03','福岡','17:10','羽田','18:40'),
 ('FH04','福岡','21:10','羽田','22:40'),
 ('CF01','中部','10:00','福岡','11:20'),
 ('CF02','中部','16:00','福岡','17:20'),
 ('FC01','福岡','10:00','中部','11:20'),
 ('FC02','福岡','16:00','中部','17:20'),
 ('KF01','関西','08:10','福岡','09:20'),
 ('KF02','関西','11:10','福岡','12:20'),
 ('KF03','関西','19:10','福岡','20:20'),
 ('FK01','福岡','08:10','関西','09:20'),
 ('FK02','福岡','11:10','関西','12:20'),
 ('FK03','福岡','19:10','関西','20:20');

WITH RECURSIVE temp (departure, depart_at, arrival, arrive_at, path, boarding_times, last_time)
AS (
 SELECT departure, depart_at, arrival, arrive_at, CONCAT(departure,' ',depart_at,' -[',name,']-> ',arrival,' ',arrive_at) AS path, 1 AS boarding_times, arrive_at FROM planes WHERE departure='羽田'
 UNION ALL SELECT temp.departure, temp.depart_at, planes.arrival, planes.arrive_at, CONCAT(temp.path,"\n",planes.departure,' ',planes.depart_at,' -[',planes.name,']-> ',planes.arrival,' ',planes.arrive_at), temp.boarding_times+1, planes.arrive_at FROM temp, planes WHERE temp.arrival=planes.departure AND temp.last_time<planes.depart_at
)
SELECT departure, depart_at, arrival, arrive_at, path, boarding_times FROM temp WHERE arrival = '千歳' ORDER BY arrive_at;