what would be the best way to make the id field md5 of username, body, msg_date fields in a table?

structure


CREATE TABLE IF NOT EXISTS `blabbing` (   
 `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 NOT NULL,
   `msg_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
   `body` varchar(255) CHARACTER SET utf8 NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=307 ;

Also if I want to add a responseto field can I use 开发者_如何学Gothe same structure as the body field because this is a response and will have the same type of data that the field.

Not entirely sure what you are asking, but I think you want a field to contain an MD5 of other values you are inserting, and want the database to handle it. You can try something like this:

CREATE TRIGGER blabbing_insert BEFORE INSERT ON `blabbin` FOR EACH ROW SET NEW.id = md5(...);

As others are alluding to, if this is just to ensure uniqueness, there are probably better ways.

CREATE TRIGGER trig_tablename_insert
BEFORE INSERT ON `tablename`
FOR EACH ROW SET NEW.id = md5(username + body + CAST(msg_date AS CHAR));

Since this is likely a primary key, using a hash function on an indexed column should be avoided. Hash functions are not sequential, so an ordered index will get fragmented VERY quickly.

Your best bet is to create a trigger to check the values don't already exist, or put extra code as part of the insert. It will make for much faster inserts. Use an EXISTS with a subquery searching for a match.

To avoid duplicates do a query and check if it returns false or true

SELECT body FROM blabbing WHERE username=X ORDER BY msg_date ASC LIMIT 1