List:General Discussion« Previous MessageNext Message »
From:Martin Koch Date:April 30 2013 3:17pm
Subject:Long integer constant problem in views
View as plain text  
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