title: MySQL how to compare a HEX type column to a string representing a hex value
body: <p>I have a database view which has a column that is converted from bigint to hex.  I also have the following Stored Procedure:</p>
  
  <pre><code>CREATE PROCEDURE giveMeRows(pId varchar(64))
     BEGIN
       select * viewRowGiver Where Column1 = pId
     END
  </code></pre>
  
  <p>For the view in the stored procedure above, "Column1" is a HEX value.  "pId" is a string value passed in from a webpage call.  To make it more clear, "pId" is the text representation of Column1.  However, when used in the stored procedure above, the result is zero rows returned (it should return at least 1)</p>
  
  <p>I'm guessing that comparing a HEX to a String is not the way to go.  </p>
  
  <p>Comparing like this does not work:</p>
  
  <blockquote>
    <p>Column1 = HEX(pId)</p>
  </blockquote>
  
  <p>And comparing like this takes a rather long time (upwards of 20 seconds) when you have hundreds of rows in the underlying tables:</p>
  
  <blockquote>
    <p>CONV(Column2, 16, 10) = CONV(pId, 16, 10)</p>
  </blockquote>
  
  <p>Any ideas how to fix this so that it works without taking so much time?</p>
  
  <p>EDIT TO SHOW THE WHOLE PROCESS WITH SAMPLE DATA:</p>
  
  <pre><code>**BASE TABLE** 
  CREATE TABLE `BaseTable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `DevID` bigint(20) unsigned DEFAULT NULL,
    `Nickname` varchar(256) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniqueOne` (`DevID`,`ArrayIndex`)
  ) ENGINE=InnoDB AUTO_INCREMENT=196742 DEFAULT CHARSET=latin1;
  
  Produces this kind of data
  1355742314703884    Item 1
  1090982514412804    Item 2
  1100905476801632    Item 3
  819808395279156     Item 4
  947267619541158     Item 5
  978328868008274     Item 6
  45394831601695870   Item 7
  
  **VIEW (this view is also called to get all rows to be displayed on a website)**
  CREATE VIEW viewFromBaseTable
    SELECT HEX(DevID) as Column1, Nickname FROM BaseTable
  
  Produces this kind of data
  4D10A5B132C0C   Item 1
  3E03E293A4D04   Item 2
  3E94487E04460   Item 3
  2E99C842F3B34   Item 4
  35D88EDF850A6   Item 5
  379C8F0A6B152    Item 6
  A1465BDC41FC7E   Item 7
  
  **STORED PROCEDURE(the where clause here works, albeit slower than it should)**
  CREATE PROCEDURE procGetRowsById(pId varchar(64))
    BEGIN
      SELECT * FROM viewFromBaseTable WHERE CONV(Column1, 16, 10) = CONV(pId, 16, 1)
    END
  </code></pre>
  
  <p>The code from the website which calls stored procedure "procGetRowsById" send in pId as a "string" representation of the "hex" values produced in viewFromBaseTable.  Hope that clarifies the process.</p>