mysql - 创建 VIEW 但更改所选列的数据类型

Question

我已经成功创建了 MySQL View ，但刚刚注意到大多数列的数据类型是“longtext”。我需要将其中一些更改为整数，但看起来在创建 View 后无法更改 View 列的数据类型。

创建 View 时是否可以指定各个列的数据类型？这是我的 SQL 查询:

select p.ID as order_id,
       p.post_date,
       max(CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id
                     THEN pm.meta_value END) as billing_email,
       max(CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id
                     THEN pm.meta_value END) as _billing_first_name,
       max(CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id
                     THEN pm.meta_value END) as _billing_last_name,
       max(CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id
                     THEN pm.meta_value END) as order_total,
       max(CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id
                     THEN pm.meta_value END) as order_tax,
       max(CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id
                     THEN pm.meta_value END) as paid_date
from wp_posts as p,
     wp_postmeta as pm
where post_type = 'shop_order'
  and p.ID = pm.post_id
  and
  and post_status = 'wc-completed'
group by p.ID

我希望 order_tax 和 order_total 列为整数(例如 bigint(20))。一般来说，我对 SQL 不太熟悉，无法找到可能的语法？

Answer 1

谢谢 - 我能够使用 CAST 正确设置 View 数据类型，例如:

    CAST(max( CASE WHEN pm.meta_key = '_line_subtotal' and p.order_item_id = pm.order_item_id THEN pm.meta_value END ) AS UNSIGNED) as subtotal