1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
SET collation_connection = latin1_general_ci
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `point`
--------------
--------------
CREATE TABLE `point`
( `num` smallint unsigned NOT NULL,
`ident` smallint unsigned NOT NULL,
`position` integer unsigned NOT NULL,
`points` integer unsigned NOT NULL,
`pays` char(02) NOT NULL,
`etat` char(04) NOT NULL,
`id_count` integer unsigned NOT NULL,
`id_pa` integer unsigned NOT NULL,
primary key (`num`,`position`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
drop procedure if exists `load_table`
--------------
--------------
create procedure `load_table` ()
DETERMINISTIC
NO SQL
BEGIN
DECLARE _fin INTEGER UNSIGNED DEFAULT 1;
DECLARE _line VARCHAR(255) DEFAULT "";
DECLARE _line1 VARCHAR(255) DEFAULT "insert into `point` (`num`,`ident`,`position`,`points`,`pays`,`etat`,`id_count`,`id_pa`) select ";
DECLARE _line2 VARCHAR(255) DEFAULT ",position,points,pays,etat,id_count,id_pa from fred.";
DECLARE _name VARCHAR(255);
DECLARE _num INTEGER UNSIGNED;
DECLARE _rang INTEGER UNSIGNED;
DECLARE _tab CURSOR FOR SELECT table_name, cast(concat(substr(table_name,8,2), right(concat('00',substr(table_name,11,2)), 2)) as unsigned) as num
FROM INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'fred' ORDER BY table_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin = 0;
SET _rang = 1;
OPEN _tab;
FETCH _tab INTO _name, _num;
WHILE (_fin)
DO
SET @line = concat(_line1, _rang, ',', _num, _line2, _name, ';');
prepare stmt from @line;
execute stmt;
deallocate prepare stmt;
SET _rang = _rang + 1;
FETCH _tab INTO _name, _num;
END WHILE;
CLOSE _tab;
END
--------------
--------------
CALL load_table()
--------------
--------------
select * from point limit 20
--------------
+-----+-------+----------+-----------+------+------+----------+-------+
| num | ident | position | points | pays | etat | id_count | id_pa |
+-----+-------+----------+-----------+------+------+----------+-------+
| 1 | 0 | 1 | 276007310 | en | | 149311 | 117 |
| 1 | 0 | 2 | 254238427 | de | v | 158181 | 115 |
| 1 | 0 | 3 | 188812762 | pl | | 152918 | 117 |
| 1 | 0 | 4 | 167926906 | pl | v | 153346 | 117 |
| 1 | 0 | 5 | 145092014 | en | | 149591 | 117 |
| 1 | 0 | 6 | 131578049 | fr | v | 155773 | 119 |
| 1 | 0 | 7 | 123976590 | fr | | 121003 | 119 |
| 1 | 0 | 8 | 112836423 | de | | 101898 | 105 |
| 1 | 0 | 9 | 109529870 | es | | 112285 | 106 |
| 1 | 0 | 10 | 108126945 | en | | 149596 | 117 |
| 1 | 0 | 11 | 102640762 | de | | 108505 | 105 |
| 1 | 0 | 12 | 101374657 | fr | | 132023 | 123 |
| 1 | 0 | 13 | 98328084 | pl | | 153342 | 117 |
| 1 | 0 | 14 | 97842241 | de | | 108255 | 105 |
| 1 | 0 | 15 | 97629294 | en | v | 149810 | 117 |
| 1 | 0 | 16 | 97560494 | es | | 146939 | 106 |
| 1 | 0 | 17 | 97536327 | tr | | 100250 | 116 |
| 1 | 0 | 18 | 96715217 | fr | | 165655 | 106 |
| 1 | 0 | 19 | 96174100 | pl | | 153038 | 117 |
| 1 | 0 | 20 | 95251681 | fr | | 125176 | 106 |
+-----+-------+----------+-----------+------+------+----------+-------+
--------------
ANALYZE TABLE `point`
--------------
+------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+---------+----------+----------+
| base.point | analyze | status | OK |
+------------+---------+----------+----------+
--------------
SHOW INDEX FROM `point`
--------------
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| point | 0 | PRIMARY | 1 | num | A | 219 | NULL | NULL | | BTREE | | |
| point | 0 | PRIMARY | 2 | position | A | 1210490 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--------------
SHOW TABLE STATUS like '%point%'
--------------
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
| point | InnoDB | 10 | Compressed | 1210490 | 25 | 31244288 | 0 | 0 | 4718592 | NULL | 2017-02-11 20:55:20 | 2017-02-11 20:56:08 | NULL | latin1_general_ci | NULL | row_format=COMPRESSED | |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------------+---------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager