MySQL视图中的BLOB而不是正确的数据 [英] BLOB in MySQL view instead of the proper data

问题描述

我使用UNION ALL从两个表中创建了一个MySQL视图，以便为那些表中的相同数据(例如tbl1.author2 AS translator ... tbl2.translator AS translator)获得相同的列名，依此类推，问题是当我尝试从该视图中选择某项内容时，一半的数据显示为BLOB，而不是原始值.

视图定义为:

I created a MySQL view using UNION ALL from two tables so that I would get the same column names for the same data in those tables (i.e. tbl1.author2 AS translator ... tbl2.translator AS translator) and so on, the problem is when I try to select something from that view, half of the data comes out as BLOB, instead of the original value.

The view definition is:

SELECT e.id AS prod_id, 
e.price_vat AS price_vat, 
e.product AS title, 
e.authors AS author, 
e.isbn AS isbn, 
e.ean AS ean, 
e.page_count AS page_count, 
e.publishers AS publishers, 
e.issue_year AS issue_year,
'e' AS type
FROM ama_euromedia_products AS e
UNION ALL
SELECT
k.publishers AS publishers, 
DATE_FORMAT(k.publication_date, '%Y') AS issue_year,
k.ean AS ean, 
k.number_of_pages AS page_count, 
k.author AS author, 
k.isbn AS isbn, 
k.title_full AS title, 
k.price_amount AS price_vat, 
k.internal AS prod_id,
'k' AS type
FROM ama_kosmas_products AS k 

表定义:

CREATE TABLE `ama_euromedia_products` (
  `id` int(9) NOT NULL,
  `product` text COLLATE utf8_czech_ci NOT NULL,
  `isbn` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `authors` text COLLATE utf8_czech_ci,
  `publishers` text COLLATE utf8_czech_ci,
  `price` float(9,0) DEFAULT NULL,
  `price_vat` float(9,0) DEFAULT NULL,
  `vat` int(3) DEFAULT NULL,
  `availability` tinyint(1) DEFAULT NULL,
  `genres` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `ean` bigint(14) DEFAULT NULL,
  `page_count` int(7) DEFAULT NULL,
  `issue_year` int(4) DEFAULT NULL,
  `supply_date` timestamp NULL DEFAULT NULL,
  `width` int(7) DEFAULT NULL,
  `height` int(7) DEFAULT NULL,
  `weight` int(7) DEFAULT NULL,
  `binding` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `long_v` text COLLATE utf8_czech_ci,
  `short` text COLLATE utf8_czech_ci,
  `imgurl` text COLLATE utf8_czech_ci,
  `is_preliminary` tinyint(1) DEFAULT NULL,
  `stack_date` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `invoice_only` tinyint(1) DEFAULT NULL,
  `new` tinyint(1) DEFAULT NULL,
  `sale` tinyint(1) DEFAULT NULL,
  `return_v` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `indexy` (`id`,`isbn`,`genres`,`ean`,`issue_year`,`supply_date`,`stack_date`,`new`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


CREATE TABLE `ama_kosmas_products` (
  `id` int(7) NOT NULL AUTO_INCREMENT,
  `internal` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `isbn` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `ean` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `contained_items` text COLLATE utf8_czech_ci,
  `title_original` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `title_full` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `subtitle` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `other_text` text COLLATE utf8_czech_ci,
  `languages` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `author` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `illustrator` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `translator` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `number_of_pages` int(7) DEFAULT NULL,
  `subject_scheme_name` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `subject_code` int(7) DEFAULT NULL,
  `subject_heading_text` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `media_file_label` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `media_file_thumbnail` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `publishers` text COLLATE utf8_czech_ci,
  `publishing_status` tinyint(2) DEFAULT NULL,
  `publication_date` timestamp NULL DEFAULT NULL,
  `product_availability` tinyint(2) DEFAULT NULL,
  `on_hand` int(7) DEFAULT NULL,
  `on_order` int(7) DEFAULT NULL,
  `price_amount` int(7) DEFAULT NULL,
  `price_tax_rate_code` varchar(1) COLLATE utf8_czech_ci DEFAULT NULL,
  `price_tax_rate_percent` tinyint(2) DEFAULT NULL,
  `price_tax_amount` int(7) DEFAULT NULL,
  `price_taxable_amount` int(7) DEFAULT NULL,
  `reissue_date` timestamp NULL DEFAULT NULL,
  `invoice_only` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `internal` (`internal`)
) ENGINE=MyISAM AUTO_INCREMENT=43341 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

知道为什么会这样吗?

推荐答案

使用UNION创建视图时，必须确保相应列的数据类型相同(或至少足够相似)将其中一个转换为另一个).在当前情况下，视图的第一列是publishers或price_vat，除了BLOB之外没有任何数据类型定义对此有很大的意义.

When you create a view with a UNION, you have to make sure the data types of the corresponding columns are the same (or at least, similar enough for one to be converted to the other). In the current case, the first column of the view is a publishers or a price_vat, and no data type definition apart from BLOB can make much sense of that.

如果您确实需要此视图，可以尝试...

If you really need this as a view, you could try...

SELECT e.price_vat AS price_vat, 
       NULL        AS publishers,
       ...etc...
       'e'         AS type
    FROM ama_euromedia_products AS e;
UNION ALL
SELECT
     NULL         AS price_vat,
     k.publishers AS publishers, 
     ...etc...
     'k'          AS type
     FROM ama_kosmas_products AS k;

在每列中获取一种数据类型.

to get a single data type into each column.

这篇关于MySQL视图中的BLOB而不是正确的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！