Monday, 24 September 2012

MySQL For SQL Server developers

No doubt there are millions of articles out there that cover the same thing but as per most computer science related topics, we have become so overwhelmed by the topics that I thought I would write another article.
I will update this as I go along but these are some differences I have found between MySql and SQL Server.

  1. Use MySql Workbench, which seems to be the best (well from the free tools) and gives an amount of intellisense= especially useful for learning where to put the semi-colons.
  2. The types are different. This might be obvious but although the main types are the same, like integer and varchar, there are also types that are not in both languages so be careful. Of special note is BIT which is a boolean in SQL Server but is more like CHAR in MySql, use BOOLEAN instead which takes 1 or 0.
  3. You can only use literal values for defaults, not functions. For this reason, if you want something like the current date/time, you can either use a TIMESTAMP field and the CURRENT_TIMESTAMP default (which only works for the first timestamp field!) or you need a trigger.
  4. getdate() is NOW() in MySql
  5. Lots of methods are in both languages but some have different names and parameter positions such as DATE_ADD instead of DATEADD.
  6. MySql is not clever enough to work out where a store procedure starts and finishes. For this reason, you have to write DELIMITER $$, or some other random set of characters, before CREATE PROCEDURE (not PROC) and then at the very end of the proc, after END, you put the characters like this: END$$ and then another line that changes the delimiter back to ;
  7. Parameters in MySql cannot have @s on them, they are normal cased words. If you get a conflict with a column name, you can use back ticks (') to delimit the parameters in a similar way to square brackets in SQL Server.
  8. Local variable declares in stored procs must all be the first statements after BEGIN. There is also a precedence in the types of DECLARES which must come first - Google will help you with specifics.
  9. You must put a semi-colon after every statement. In SQL Server, the interpreter can usually work out where statements start and end.
  10. You call stored procedures by using CALL StoredProcName(); and not exec
  11. You cannot use RETURN in a stored proc, you can implement it by using LEAVE
  12. Encryption is much more basic. Although they have ENCRYPT_AES like ENCRYPTBYKEY(), it only supports 128bit keys (without rebuilding the source) and does not support the authenticator functionality. Also, these are by password and don't support keys as primitive types.
  13. UUID() is not guaranteed to be unique! Kind of defeats the point.
Post a Comment