通过视图查看MySQL Tinybit(1)列 [英] MySQL Tinybit(1) column through a view

问题描述

我有一个连接2个表的视图.其中一个表具有一列tinyint(1)类型的列，表示一个布尔值.

I have a view that joins 2 tables. One of the tables has a column of type tinyint(1) representing a boolean value.

联接时，该表并不总是具有条目，因此，当缺少行时，视图需要采用0(假)值. 我希望视图公开TINYINT(1)和NOT NULL类型的列，因为它将始终具有默认值0，但是我没有成功.

This table does not always have an entry when joining, so the view needs to take a value of 0 (false) when the row is missing. I would like the view to expose a column of type TINYINT(1) and NOT NULL, since it will always have a default value of 0, but I haven't succeeded in doing so.

请参见下面的SQL，它重现了该问题.

See SQL below that reproduces the issue.

CREATE TABLE TEST1 (
    ID bigint(20) NOT NULL AUTO_INCREMENT,  
    PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=10202961 DEFAULT CHARSET=utf8;

CREATE TABLE TEST2 (
    TEST1_ID bigint(20) NOT NULL, 
    MY_BOOLEAN TINYINT(1) NOT NULL DEFAULT '0' 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

DROP FUNCTION get_tinyint_zero;
CREATE FUNCTION get_tinyint_zero() RETURNS TINYINT(1) RETURN 0;

CREATE OR REPLACE VIEW TEST_VIEW
AS
    SELECT 
        T1.ID AS ID,
        COALESCE(T2.MY_BOOLEAN, get_tinyint_zero()) AS MY_BOOLEAN
    FROM
        TEST1 T1 LEFT JOIN TEST2 T2 ON T1.ID=T2.TEST1_ID;

DESC TEST2;
DESC TEST_VIEW;

在TEST2

# Field, Type, Null, Key, Default, Extra
'MY_BOOLEAN', 'tinyint(1)', 'NO', '', '0', ''

在TEST_VIEW中

In TEST_VIEW

# Field, Type, Null, Key, Default, Extra
'MY_BOOLEAN', 'int(4)', 'YES', '', NULL, ''

推荐答案

https://www.db-fiddle.com/f/igDh3aJEXRLZEkL7eNrcnc/0

CREATE FUNCTION get_tinyint(MY_BOOLEAN tinyint) RETURNS TINYINT(1) 
RETURN COALESCE(MY_BOOLEAN, 0);

CREATE OR REPLACE VIEW TEST_VIEW AS
SELECT 
    T1.ID AS ID,
    get_tinyint(T2.MY_BOOLEAN) AS MY_BOOLEAN
FROM
    TEST1 T1 LEFT JOIN TEST2 T2 ON T1.ID=T2.TEST1_ID;

这篇关于通过视图查看MySQL Tinybit(1)列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！