UNION / UNION ALL su campi int unsigned zerofill.

Tip scritto da DebUsr 11 mesi fa

Utilizzando UNION / UNION ALL su campi int unsigned zerofill, ho notato che Mysql considera i campi int unsigned zerofill come int tagliando gli zeri, per evitare la perdita degli zeri dovuta alla conversione implicita bisogna utilizzare la funzione: [code] CONVERT(`tuocampo`,CHAR) [/code] In questo modo v'errano mantenuti gli zeri.

  1/* Struttura e dati */
  2CREATE TABLE IF NOT EXISTS `tab_cap_A` (
  3  `id_cap` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  4  `cap` mediumint(5) unsigned zerofill NOT NULL,
  5  PRIMARY KEY (`id_cap`),
  6  KEY `cap` (`cap`)
  7) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Test tab cap A' AUTO_INCREMENT=1;
  8
  9INSERT INTO `tab_cap_A` VALUES
 10(1, 00010),
 11(2, 00010),
 12(3, 00010),
 13(4, 00010),
 14(5, 00010),
 15(6, 00010);
 16
 17CREATE TABLE IF NOT EXISTS `tab_cap_B` (
 18  `id_cap` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 19  `cap` mediumint(5) unsigned zerofill NOT NULL,
 20  PRIMARY KEY (`id_cap`),
 21  KEY `cap` (`cap`)
 22) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Test tab cap B' AUTO_INCREMENT=1;
 23
 24INSERT INTO `tab_cap_B` VALUES
 25(1, 00011),
 26(2, 00011),
 27(3, 00011),
 28(4, 00011),
 29(5, 00011),
 30(6, 00011);
 31
 32
 33/* Query tab_cap_A */
 34SELECT
 35 `id_cap`
 36, `cap`
 37, LENGTH(`cap`) AS `length_cap` 
 38FROM `tab_cap_A`;
 39
 40/*
 41+--------+-------+------------+
 42| id_cap | cap   | length_cap |
 43+--------+-------+------------+
 44|      1 | 00010 |          5 |
 45|      2 | 00010 |          5 |
 46|      3 | 00010 |          5 |
 47|      4 | 00010 |          5 |
 48|      5 | 00010 |          5 |
 49|      6 | 00010 |          5 |
 50+--------+-------+------------+
 51*/
 52
 53/* Query tab_cap_B */
 54SELECT
 55 `id_cap`
 56, `cap`
 57, LENGTH(`cap`) AS `length_cap` 
 58FROM `tab_cap_B`; 
 59
 60/*
 61+--------+-------+------------+
 62| id_cap | cap   | length_cap |
 63+--------+-------+------------+
 64|      1 | 00011 |          5 |
 65|      2 | 00011 |          5 |
 66|      3 | 00011 |          5 |
 67|      4 | 00011 |          5 |
 68|      5 | 00011 |          5 |
 69|      6 | 00011 |          5 |
 70+--------+-------+------------+
 71*/
 72
 73/* UNION ALL */
 74SELECT
 75 `id_cap`
 76, `cap`
 77, LENGTH(`cap`) AS `length_cap` 
 78FROM `tab_cap_A` 
 79UNION ALL 
 80SELECT
 81 `id_cap`
 82, `cap`
 83, LENGTH(`cap`) AS `length_cap` 
 84FROM `tab_cap_B`;
 85
 86/*Taglia gli zeri*/
 87/*
 88+--------+-----+------------+
 89| id_cap | cap | length_cap |
 90+--------+-----+------------+
 91|      1 |  10 |          5 |
 92|      2 |  10 |          5 |
 93|      3 |  10 |          5 |
 94|      4 |  10 |          5 |
 95|      5 |  10 |          5 |
 96|      6 |  10 |          5 |
 97|      1 |  11 |          5 |
 98|      2 |  11 |          5 |
 99|      3 |  11 |          5 |
100|      4 |  11 |          5 |
101|      5 |  11 |          5 |
102|      6 |  11 |          5 |
103+--------+-----+------------+
104*/

La soluzione:

 1/*OK*/
 2SELECT
 3 `id_cap`
 4, CONVERT(`cap`,CHAR) AS `cap`
 5, LENGTH(`cap`) AS `length_cap` 
 6FROM `tab_cap_A` 
 7UNION ALL 
 8SELECT
 9 `id_cap`
10, CONVERT(`cap`,CHAR) AS `cap`
11, LENGTH(`cap`) AS `length_cap` 
12FROM `tab_cap_B`;
13
14/*
15+--------+-------+------------+
16| id_cap | cap   | length_cap |
17+--------+-------+------------+
18|      1 | 00010 |          5 |
19|      2 | 00010 |          5 |
20|      3 | 00010 |          5 |
21|      4 | 00010 |          5 |
22|      5 | 00010 |          5 |
23|      6 | 00010 |          5 |
24|      1 | 00011 |          5 |
25|      2 | 00011 |          5 |
26|      3 | 00011 |          5 |
27|      4 | 00011 |          5 |
28|      5 | 00011 |          5 |
29|      6 | 00011 |          5 |
30+--------+-------+------------+
31*/

Current rating: 0.0/5
1
2
3
4
5

Tip precedente: Report con alias di campo dinamici