generating MD5 idHash directly in MySQL statement

In my table I have an userID that is auto-incremented. In the same row I have an idHash. Is it possible to generate the idHash (simply an MD5 sum) from it dire开发者_运维知识库ctly with the same INSERT statement so that I don't have to SELECT the id, and then UPDATE the idHash again?

Problem is: I do not know the userID before it is being generated (auto-incremented) by MySQL.

Thanks Frank

PS: I'm using PHP. PPS: This question is all about a SINGLE INSERT. I know that I can use PHP or other languages to manually select the data and then update it.

I don't believe you can do it within a single INSERT statement.

What you probably could do is use an INSERT trigger, that both determines the new ID, hashes it, and then updates the record.

One solution I can recommend is using the last insert ID instead of re-querying the table. Here is a simplified example:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "INSERT INTO users VALUES (....)";
$mysqli->query($query);

$newUserID = $mysqli->insert_id;

$query = "UPDATE users SET idHash = MD5(userID) WHERE userID = $newUserID";
$mysqli->query($query);

/* close connection */
$mysqli->close();
?>

AFAIK there's no "secure" way for doing this in the same query if you're using auto_increment.

However, if rows are never deleted in your table, you can use this little trick :

insert into mytable (col1, col2, col3, idhash) 
values ('', '', '', md5(select max(id) from mytable))

I don't understand why you need to hash the id though, why not use the id directly ?

This seems to work for me:

CREATE TABLE tbl (id INT PRIMARY KEY AUTO_INCREMENT, idHash TEXT);
INSERT INTO tbl (idHash) VALUES (MD5(LAST_INSERT_ID() + 1));
SELECT *, MD5(id) FROM tbl;

Note this will only work on single-row inserts as LAST_INSERT_ID returns the insert ID of the first row inserted.

Performing MD5(column_name) on an auto_increment value does not work as the value has not been generated yet, so it is essentially calling MD5(0).

PHP snippet

<?
$tablename      = "tablename";
$next_increment     = 0;
$qShowStatus        = "SHOW TABLE STATUS LIKE '$tablename'";
$qShowStatusResult  = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . $qShowStatus );

$row = mysql_fetch_assoc($qShowStatusResult);
$next_increment = $row['Auto_increment'];

echo "next increment number: [$next_increment]";
?>

This will get you the next auto-increment and then you can use this in your insert.

Note: This is not perfect (Your method is imperfect as you will effectively have 2 primary keys)

From: http://blog.jamiedoris.com/geek/560/