List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 30 2013 11:05pm
Subject:RE: Long integer constant problem in views
View as plain text  
  WHERE id = UNHEX('36a461c81cab40169791f49ad65a3728')

> -----Original Message-----
> From: Martin Koch [mailto:mak@stripped]
> Sent: Tuesday, April 30, 2013 8:18 AM
> To: mysql@stripped
> Subject: Long integer constant problem in views
> 
> Hi List
> 
> I have a table with a primary key with type binary(16) and a few
> columns.
> I'd trying to create a view that looks up all rows with a particular
> key, i.e. something like
> 
> CREATE OR REPLACE VIEW foo AS
>   SELECT *
>   FROM mytable
>   WHERE id = X'36a461c81cab40169791f49ad65a3728';
> 
> I use the mysql command line client to create the view. When I now
> inspect the view using, say, mysql workbench, the id has been mangled
> in the where clause which now reads
> 
> WHERE (`mytable`.`id` = 0x9791f49ad65a37)
> 
> I then tried expressing the id as something that will fit within 8
> bytes
> 
> WHERE id = X'36a461c81cab4016' << 16 | X'9791f49ad65a3728';
> 
> Unfortunately, this makes the query much slower.
> 
> I have now worked around this in another way, but I'd like to know if
> there is a way of doing this?
> 
> Thanks,
> /Martin Koch
Thread
Long integer constant problem in viewsMartin Koch30 Apr
  • RE: Long integer constant problem in viewsRick James30 Apr
  • Re: Long integer constant problem in viewshsv1 May