May 31, 2012 at 11:46 pm
Comments posted to this topic are about the item 10 tips on converting MS SQL queries to MySQL
June 1, 2012 at 2:20 am
I see no valid reason to why you'd want to do this.
If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
June 1, 2012 at 3:05 am
If only the world/clients were that easy to work with, IceDread 😉 I would never want to do it out of personal choice!
Lucky you if you've never had a client adamant that your MSSQL app will have to be converted to use another database type if it's going to touch their network/servers!
June 1, 2012 at 3:14 am
FortyEightK (6/1/2012)
If only the world/clients were that easy to work with, IceDread 😉 I would never want to do it out of personal choice!Lucky you if you've never had a client adamant that your MSSQL app will have to be converted to use another database type if it's going to touch their network/servers!
I was merely thinking about the specific of moving to mysql, not moving from one db to another.
I've actually never moved/ converted one database to another database type all thou we have a big system in oracle that we might move to mssql.
June 1, 2012 at 3:17 am
I don't really like this article, as a lot of the tips are lacking background information or giving less than optimal advise.
Note that I have no hands-on experience with MySQL; the below comments are based on what I found on internet.
1) Sometime MS SQL table or column names are enclosed in square brackets in queries (e.g. if contains spaces or for some other reasons). MySQL does not allow square brackets around table of column names, they all must be replaced by ` symbol or cut off: [object] -> `object`.
The square brackets used by SQL Server are a hideous deviation from the ANSI standard. And there is no reason why everyone keeps using them, as the ANSI standard delimiter, double quotes, is supported by SQL Server as well. So unless you are really sure that you'll never port to other databases, avoid the [square brackets] and uses "quotation marks" instead. (Or, better yet, stick to identifier names that don't need delimiters).
MySQL also supports ANSI-standard delimiters - but only if it is running in ANSI mode. Otherwise, you will have to convert your code to use the non-standard back ticks (but why would you not run in ANSI mode when you are developing a project that needs to be as compatible as possible across products?)
3) CONVERT() function is used to convert an expression of one data type to another in Microsoft SQL. In MySQL CONVERT() function converts text data between different character sets. However, there is equivalent function CAST(), so every occurrence of 'convert(%type%, %expression%)' in MS SQL query must be replaced by 'cast(%expression% AS %type%)' in MySQL query.
Because CAST is ANSI-standard and CONVERT is not, I prefer to use CAST anyway. However, CONVERT gives more control, for instance when converting date values to string values in a specific format. For MySQL, you'd have too convert to DATE_FORMAT to get the same results.
4) LEN() function returns length of string expression in MS SQL. MySQL equivalent for this function is LENGTH().
Unless I misunderstand the MySQL documentation, their LENGTH() is actually equivalent to SQL Servers DATALENGTH() (number of bytes in storage, so twice the number of characters for Unicode). SQL Server's LEN() returns the number of characters; MySQL uses CHAR_LENGTH() for that.
6) GETDATE() function returns the current system date and time in MS SQL. MySQL equivalent for this function is NOW().
Or use ANSI standard CURRENT_TIMESTAMP, which works on both databases.
8) MS SQL function CONTAINS(expression, template) searches for matches of template inside expression. MySQL has operator LIKE that implements the same semantics: expression LIKE %template%
I'd say that the MySQL expression LIKE '%template%' is actually equivalent to the MS SQL Server expression LIKE '%template%' - yes, those are equal, and they are ANSI.
The CONTAINS function only works when full-text search is used and set up (which takes some effort, and introduces some overhead - so use with care!), and it allows for much more than the simple pattern matching of LIKE. I have not tried to find out if MySQL offers anything to replace SQL Server's Full-Text Search capabilities.
9) If MS SQL query contains 'TOP (100) PERCENT' pattern just cut it off when composing MySQL query. If there is another percentage amount in that pattern, it can be replace by the following code in MySQL (works in MySQL 5.0.7 and higher):
The first part of that sentence can be shortened to "If MS SQL query contains 'TOP (100) PERCENT' pattern just cut it off". It's just a waste of bytes.
The replacement pattern looks quite inefficient to me (as the query has to be executed twice), but since I never used MySQL, I can't verify it. I'd also be concerned about the risk of SQL injection, since it uses dynamic SQL.
10) Syntax of JOIN constructions are very similar in MS SQL and MySQL. The only difference is that MS SQL keyword 'WHERE' is replaced by 'ON' in MySQL. For example:
While the example given will probably work, this recommendation as a whole is pure nonsense. For most join types (inner, left outer, right outer, full outer), the syntax is exactly the same. Both SQL Server and MySQL require an ON clause to be present, and both SQL Server and MySQL optionally allow a WHERE clause as well. In a cross join (as given in the example), there is indeed a slight difference - SQL Server does not allow an ON clause and does allow a WHERE clause; MySQL also allows a WHERE clause, but optionally allows an ON clause as well. Since this is completely non-ANSI, I recommend against using this non-standard option and sticking to the WHERE clause.
It then became even more interesting when I followed the link - and found a website from a vendor that sells conversion tools, but also includes some articles. Including an exact (word for word) copy of this article.
My conclusions:
1. I'll never buy a product from "Intelligent Converters", nor advise a client to do so - given the amount of bad advice given in their article, I don't think I'll like the results of their automated conversion tools.
2. Unless the author of this SQL Server Central article works for "Intelligent Converters", submitting this article is a flagrant breach of copyright.
June 1, 2012 at 4:06 am
IceDread (6/1/2012)
I see no valid reason to why you'd want to do this.If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
The reason is that MySQL is free, SQL Server has massive licensing costs.
June 1, 2012 at 4:12 am
Jonathan AC Roberts (6/1/2012)
IceDread (6/1/2012)
I see no valid reason to why you'd want to do this.If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
The reason is that MySQL is free, SQL Server has massive licensing costs.
Express Edition?
June 1, 2012 at 4:16 am
+1 hugo
it's also a complete re-hash of an article posted 30 days ago
http://www.sqlservercentral.com/articles/MySQL/88971/
both referencing the website convert-in.com
perhaps they should pay for an advert rather than posting articles with inaccurate content 😎
MVDBA
June 1, 2012 at 4:33 am
michael vessey (6/1/2012)
+1 hugoit's also a complete re-hash of an article posted 30 days ago
http://www.sqlservercentral.com/articles/MySQL/88971/
both referencing the website convert-in.com
perhaps they should pay for an advert rather than posting articles with inaccurate content 😎
That article was on moving in the other direction (MySQL to MS SQL Server). It had about the same level of "accuracy" (see my comment on that article). And you are right that that article is also copied from the vendor site (though by a different author than this article).
June 1, 2012 at 4:34 am
Hugo Kornelis (6/1/2012)
Jonathan AC Roberts (6/1/2012)
IceDread (6/1/2012)
I see no valid reason to why you'd want to do this.If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
The reason is that MySQL is free, SQL Server has massive licensing costs.
Express Edition?
Yes, this is one option if the cpu limits and data size limitations of Express fit your requirements. But if you require something more powerful than express edition, what do you do?
June 1, 2012 at 4:38 am
Jonathan AC Roberts (6/1/2012)
Hugo Kornelis (6/1/2012)
Jonathan AC Roberts (6/1/2012)
IceDread (6/1/2012)
I see no valid reason to why you'd want to do this.If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
The reason is that MySQL is free, SQL Server has massive licensing costs.
Express Edition?
Yes, this is one option if the cpu limits and data size limitations of Express fit your requirements. But if you require something more powerful than express edition, what do you do?
postgress:-D
MVDBA
June 1, 2012 at 4:41 am
Jonathan AC Roberts (6/1/2012)
Hugo Kornelis (6/1/2012)
Jonathan AC Roberts (6/1/2012)
IceDread (6/1/2012)
I see no valid reason to why you'd want to do this.If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
The reason is that MySQL is free, SQL Server has massive licensing costs.
Express Edition?
Yes, this is one option if the cpu limits and data size limitations of Express fit your requirements. But if you require something more powerful than express edition, what do you do?
joking aside - if any company wants to run enterprise scale applications on open source or free software then i'd start looking for another company. They sure as hell aren't going to invest in you if they don't invest in their tools.
if it's not an enterprise scale application then really mysql is proboably as good a choice as any (you have oracle express, sql express, MongoDB)
but your comments about cpu limitations etc lead me to indicate that your company might be trying to to do something big with innapropriate tools ???
MVDBA
June 1, 2012 at 4:48 am
michael vessey (6/1/2012)
Jonathan AC Roberts (6/1/2012)
Hugo Kornelis (6/1/2012)
Jonathan AC Roberts (6/1/2012)
IceDread (6/1/2012)
I see no valid reason to why you'd want to do this.If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
The reason is that MySQL is free, SQL Server has massive licensing costs.
Express Edition?
Yes, this is one option if the cpu limits and data size limitations of Express fit your requirements. But if you require something more powerful than express edition, what do you do?
joking aside - if any company wants to run enterprise scale applications on open source or free software then i'd start looking for another company. They sure as hell aren't going to invest in you if they don't invest in their tools.
if it's not an enterprise scale application then really mysql is proboably as good a choice as any (you have oracle express, sql express, MongoDB)
but your comments about cpu limitations etc lead me to indicate that your company might be trying to to do something big with innapropriate tools ???
Oracle bought MySQL so they could offer it for use along side Oracle databases. There are cases where you need a fast read-only database to run along side your enterprise SQL Server database. You could get a multiprocessor SQL Server machine with a $1000 a month per cpu license fee or you could find an open source database that would do the necessary job for free.
June 1, 2012 at 5:36 am
Jonathan AC Roberts (6/1/2012)
IceDread (6/1/2012)
I see no valid reason to why you'd want to do this.If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
The reason is that MySQL is free, SQL Server has massive licensing costs.
Because it's free does not make it good. There are better free options available.
June 1, 2012 at 6:21 am
IceDread (6/1/2012)
Jonathan AC Roberts (6/1/2012)
IceDread (6/1/2012)
I see no valid reason to why you'd want to do this.If you want to go from MS Sql server, you certainly do not want to go to Oracle MySql.
The reason is that MySQL is free, SQL Server has massive licensing costs.
Because it's free does not make it good. There are better free options available.
I'm not an expert in open-source/free databases and which ones are better or worse. Can you provide a list of ones that are better and why they are better?
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply