How to hash auto increments in mysql

问题:

Is there a way to make mysql hash the auto increment values for its tables? For example with md5?

    id - name
    1  - New York  
    2  - Chicago   
    3  - Sydney    
    4  - Berlin    

what I'm trying to get

    id - name 
    c4ca4238a0b923820dcc509a6f75849b - New York  
    c81e728d9d4c2f636f067f89cc14862c - Chicago   
    eccbc87e4b5ce2fe28308fd9f2a7baf3 - Sydney    
    a87ff679a2f3e71d9181a67b7542122c - Berlin    

Thanks in advance

EDIT:

I think I need to clarify the question a little more, what im trying to do is not Call the ID's but Insert them. In the moment the ID column is an int field which I will change to varchar.

I want to save the identifiers as Hashed in the DB not call them with mysql SELECT. Thanks

回答1:

If you really need this, for some reason, you can achieve it with a help of a separate table for sequencing and a BEFORE trigger

Table schemas:

CREATE TABLE table1_seq
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE table1
(id VARCHAR(32) NOT NULL DEFAULT 0, name VARCHAR(32));

The trigger

DELIMITER $$
CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq () VALUES ();
  SET NEW.id = MD5(LAST_INSERT_ID());
END$$
DELIMITER ;

Now you can insert rows into your table1 table

INSERT INTO table1 (`name`) VALUES ('New York'),('Chicago'),('Sydney'),('Berlin');

or select

SELECT * FROM table1 WHERE id = MD5(2);

Here is SQLFiddle demo

回答2:

Maybe i did not understand the question, but this is my opinion:

I think you try to create a hash table. So insert id field as hash of your data (md5(name)).

But if not, use a table trigger to generate field hash after insert.

回答3:

MySQL Docs say..

Functions cannot be added as COLUMN DEFAULTS - except for now() that is internally represented as a synonym of CURRENT_TIMESTAMP.

A best bet would be to do this...

UPDATE yourtablename SET id=MD5(id) WHERE 1=1;

or to run a TRIGGER

Something like ... (Not 100% correct... just giving you an idea)

CREATE TRIGGER generateMD5forID BEFORE INSERT ON `yourtablename` FOR EACH ROW SET id = MD5(id);

回答4:

All the answers above lack a good understanding of the need to use hashed id's.

Hashing the auto increment id violates the whole concept. The concept you want to reach is that the next id will be unpredictable from the previous.

In the idea of hashing the id the next id of the md5( 1 ) is the md5( 2 ).

If you use the names that helps.

It would be best to create the hash from several fields. The fields together should be unique to create a unique hash.

回答5:

The Answers here brought up an Idea in me.. I think that will work as the best solution for now.. but if there is someone who knows an easier way I'm glad to hear it.

Solution:

Create a new table with an auto increment field like id or count, which you will always increment first and use the last ID out of that table to hash it for the primary field of your other table.