当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - How to identify and unselect only one character of column having enum datatype?

问题描述:

I created a procedure where in I had the value of column with enum datatype asCompleteness_Type` ENUM('B', 'I', 'C', 'BC', 'BI', 'IC', 'BIC')

I wrote a procedure where I would like to display all the video game records which does not have 'I' in completeness_type column. Below is the proceudre I have written

 USE `videogame_collection_3`;

DROP procedure IF EXISTS `Get_Info_By_Games_missing_Something`;

DELIMITER $$

USE `videogame_collection_3`$$

create PROCEDURE `Get_Info_By_Games_missing_Something`(IN Completenessip VARCHAR(15))

BEGIN

SELECT

video_game.Game_Name,

video_game.Genre,

mycollection.Purchase_Date,

mycollection.Purchase_Price,

mycollection.`Condition`,

platform.Platform_Name,

mycollection.Completeness_Type

FROM

video_game

INNER JOIN

video_game_platform_mycollection ON video_game.Game_Id = video_game_platform_mycollection.Game_Id

INNER JOIN

platform ON video_game_platform_mycollection.Platform_Id = platform.Platform_Id

INNER JOIN

mycollection ON video_game_platform_mycollection.MyCollection_Id = mycollect `enter code here`ion.MyCollection_Id

where mycollection.Completeness_Type not like concat('%',Completenessip,'%');

END

$$

DELIMITER ;

I am calling procedure with

 call Get_Info_By_Games_missing_Something('I'); --- **It is working**

call Get_Info_By_Games_missing_Something('BI'); -- **Not Working**

call Get_Info_By_Games_missing_Something('BIC'); --**not working**

For the first Example: call Get_Info_By_Games_missing_Something('I');

I would like to display completeness_type column which does not contain 'I' So, the records in completeenss_type column has to be 'B', 'C', 'BC'

For the second call:

Expected output: call Get_Info_By_Games_missing_Something('BI');

I would like to display completeness_type column which does not contain 'B','I' ,'BI','IC', 'BC', 'BIC'So, the records in completenenss_type column has to be 'c'

For the Third call:call Get_Info_By_Games_missing_Something('BIC');

I would like to display completeness_type column which does not contain 'B','I', 'C', 'BI', 'IC','BC', 'BIC' So, the records in completenenss_type column has to be no records

So, could you please tell what is the mistake I am making. I got this query working for rows which does not contain only 'B', 'I' or 'C'. I just want result for rows which does not contain 'BI' or 'B' or 'I' individually

Thanks in advance

网友答案:

I don't have an answer to your exact question, but it seems to be there may be a better way. It's a little unclear what your values correspond to, but one easy way is to have three columns, B, I, and C which could be BIT type. You might have success storing it similar to how Unix permissions are stored; such that a file is stored as BIC so a row of type B would be encoded as 100, and IC would read 011. Depending on a couple of factors this may be better than the individual columns, but as an off-the-cuff answer based on what your queries look like, I suspect the discrete columns are going to be easier to code.

Then, of course, to search for a row with BI you'd search for columns WHERE `B`=1 AND `I`=1 AND `C`=0 and so on with the other searches. This is will scale better and is a better database design than using ENUM fields.

分享给朋友:
您可能感兴趣的文章:
随机阅读: