diff -c pgsql mysql (Was Database Name Case Sensitivity)
От | Joel Burton |
---|---|
Тема | diff -c pgsql mysql (Was Database Name Case Sensitivity) |
Дата | |
Msg-id | Pine.LNX.4.21.0104051615430.30426-100000@olympus.scw.org обсуждение исходный текст |
Список | pgsql-novice |
On Thu, 5 Apr 2001, Brian T. Allen wrote: > I am sure it is a Good Thing, thank you for the explanation. I will just > have to change my thinking to all lowercase and get used to it. > > Sort of on the same topic, does anyone know of a PostgreSQL primer for those > making the change from MySQL. Changing religions :} is never easy, > something to ease the transition and point out the differences and gotchas > would be very helpful. Let me not add to any flame wars here, please. MySQL might be useful for the most complicated database project in the universe, and might be scalable to counting the grains on sand on every beach. However--it's support for many 'high-end' database features is scarce (to it's credit, in its place is an extremely easy-to-install, easy-to-administer, relatively speedy and painless database that works great only practically every OS you'd care to use.) I don't have a simple list of 'gotchas' -- instead, I wanted to answer the other question. What are the non-gotchas, non-simple differences between the two. [Actually I have some gotchas:] - MySQL uses nonstandard '#' as a comment line. Pg doesn't. Use '--', both understand it - MySQL uses ' or " to quote values, as in WHERE name = "John". Pg uses only single quotes for this, double quotes are used to quote system identifiers (field name, table names, etc.) - The whole case-sensitivity issue, above. - Pg and MySQL seem to differ most in handling of dates, and the names of functions that handle dates. But (for me, at least) are more than just 'how do I do this MySQL thing in PostgreSQL', but 'is there a much better way to think about this, which MySQL never even supported.' For example: Imagine you're building a dynamic web for human resources. You want to list every current senior staff member's name, some info about them, and a list of their goals. With MySQL, you'd do something like this: (this is generic pseudo-code, it would easily translate to PHP, Zope, EmbPerl, etc.) <in sql="SELECT staffid, firstname, lastname FROM Staff WHERE datefired ISNULL and seniorstaff = TRUE"> <h1>$firstname $lastname</h1> <ul> <in sql="SELECT goalinfo FROM Goals WHERE staffid=$staffid"> <li>$goalinfo </in> </ul> </in> That's great, and it works fine. You can easily translate this to PostgreSQL. Would you want to, though? PostgreSQL has many features MySQL doesn't, like: * views * procedural languages * triggers * customizable aggregates * transactions For instance, rather than coding in the web front end the logic of is-not-fired and is-senior-staff, in PostgreSQL, I'd make a VIEW of all staff for which we want to show goals: CREATE VIEW staff_having_goals AS SELECT staffid, firstname || lastname as fullname FROM Staff WHERE datefired ISNULL and seniorstaff = TRUE ORDER BY lastname, firstname Now, my web programming doesn't have to worry about the lower level concerns. Imagine if this same list of people and goals appeared dozens of times on your site--I've moved from having this scattered in many places, to having it encapsulated in one place. PostgreSQL also allows procedural languages (perl, tcl, python [alpha], and an Oracle-alike, plpgsql). These allow you to create functions in your database (and even non-sysadmins can use them, as the functions fit in the PostgreSQL security model). [ Yes, MySQL has user functions, which last time I checked, had to be written in C, and linked into the database. A nice feature, to be sure, but VERY different from having high-level procedural languages usable w/o root privileges! ] We might use these procedural languages to create lists, handle database events (if a record is added here, automatically track this here, etc.) (You might have a function to calculate a staff member's hourly compensation from their salary, which, IMHO, *should* be a database function, not a function coded for every different web project or front-end project you had.) PostgreSQL also has transactions, which can remove some of the hairy if-an-error-happened-back-out-all-database-work code. (MySQL, to its credit, has transactions in their new MaxSQL thingie.) So: The things that are handled differently are fairly small. The real lesson is to learn about what features PostgreSQL has an figure out *why* to use them! I'd start w/the five about (views, procedural languages, triggers, customizable aggregates, transactions) and make sure that you understand exactly what they are, how to use them, and how wonderful they are. I hope this helps, and I do hope it doesn't sound condescending. I moved to PostgreSQL from using MySQL, and for several months after first playing with it, I just thought it was a bigger, more complicated database that did 'the same stuff' as MySQL. It took me a while to really realize how great the 'other' features are. Good luck! -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
В списке pgsql-novice по дате отправления: