How do I specify unique constraint for multiple columns in MySQL? I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Of course the example is just... an example. So please don't worry about the semantics. Solution: ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`); http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql

“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE” While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either: ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or INSERT IGNORE which implies an invitation for other kinds of failure to slip in unannounced. Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates

How do you set a default value for a MySQL Datetime column? How do you set a default value for a MySQL Datetime column? In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor. Answer: IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below... Previous versions can't do that with DATETIME... But you can do it with TIMESTAMP: mysql> create table test (str varchar(32),

MySQL: Large VARCHAR vs. TEXT? I've got a messages table in MySQL which records messages between users. Apart from the typical ids and message types (all integer types) I need to save the actual message text as either VARCHAR or TEXT. I'm setting a front-end limit of 3000 characters which means the messages would never be inserted into the db as longer than this. Is there a rationale for going with either VARCHAR(3000) or TEXT? There's something about just writing VARCHAR(3000) that feels somewhat counter-intuitive.

What's the difference between utf8_general_ci and utf8_unicode_ci Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance? Answer: These two collations are both for the UTF-8 character encoding. The differences are in how text is sorted and compared. Note: Since MySQL 5.5.3 you should use utf8mb4 rather than utf8. They both refer to the UTF-8 encoding, but the older utf8 had a MySQL-specific limitation preventing use of characters numbered above 0xFFFD. Accuracy utf8mb4_unicode_ci is based on the Unicode standard for sorting and comparison, which sorts accurately in a very wide

INNER JOIN ON vs WHERE clause For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse FROM table1 INNER JOIN table2 ON table1.foreignkey = table2.primarykey WHERE (some other conditions) Do these two work on the same way in MySQL? Answer : INNER JOIN is ANSI syntax which you should use. It is generally considered more readable, especially when you join lots of tables.