New to using MySQL....

[ionoxx]

I've been using SQL statements in ASP pages for quite a while, but always using MS access databases. MS Access has a type of column called AutoNumber, which is an incremental value that is automatically generated when a row is added on. Is there such a thing with MySQL? If so, how do you create a table with such a colum?

Never mind... found it... its just been puzzleing me for a bit, and as soon as i post something about it,.... i find what i was loking for.

 

[Armitage]

It's call autoincrement in mysql - look it up in the syntax for the CREATE statement.

 

[kamper]

Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

 

[Armitage]

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

 

[WildHorse]

CREATE TABLE anandtechForum
(
sequenceNumberColumn INT UNSIGNED AUTO_INCREMENT NOT NULL,
PRIMARY KEY (sequenceNumberColumn)
otherColumn1 CHAR(10),
otherColumn2 DECIMAL
)
;



(edited to fix a typo)

 

[Zugzwang152]

Originally posted by: Armitage

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

because its the new software/apps forum fad to diss mysql apparently. 😛

 

[n0cmonkey]

Originally posted by: Zugzwang152

Originally posted by: Armitage

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

because its the new software/apps forum fad to diss mysql apparently. 😛

The MySQL vs. PostgreSQL fight has been going on for a while. If it's happening here, we're finally catching up to the rest of the internet.

They both have pros and cons.

 

[Nothinman]

They both have pros and cons.

MySQL's big cons are the facts that it's barely a database, it's not ACID compliant and it doesn't support a ton of things that 'real' databases do like stored procedures, views and sub queries.

 

[kamper]

Originally posted by: Armitage

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

I like the fact that you can pull numbers from them without inserting rows. I personally found it much easier to use and the documentation on key generation was much better in general.

 

[Zugzwang152]

Originally posted by: n0cmonkey

Originally posted by: Zugzwang152

Originally posted by: Armitage

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

because its the new software/apps forum fad to diss mysql apparently. 😛

The MySQL vs. PostgreSQL fight has been going on for a while. If it's happening here, we're finally catching up to the rest of the internet.

They both have pros and cons.

only in the last week or so i've noticed that every mysql thread seems to have someone come in and crap on it by saying how much it sucks.

 

[n0cmonkey]

Originally posted by: Zugzwang152

Originally posted by: n0cmonkey

Originally posted by: Zugzwang152

Originally posted by: Armitage

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

because its the new software/apps forum fad to diss mysql apparently. 😛

The MySQL vs. PostgreSQL fight has been going on for a while. If it's happening here, we're finally catching up to the rest of the internet.

They both have pros and cons.

only in the last week or so i've noticed that every mysql thread seems to have someone come in and crap on it by saying how much it sucks.

Then AT's several YEARS behind. 😛

 

[Armitage]

Originally posted by: Nothinman

They both have pros and cons.

MySQL's big cons are the facts that it's barely a database, it's not ACID compliant and it doesn't support a ton of things that 'real' databases do like stored procedures, views and sub queries.

MySQL has sub queries since 4.something, and I just got their newsletter today announcing a 5.0 beta with views, stored procedures and triggers. I believe that it is fully ACID compliant if you use the INNODB table type. Or use the faster MyISAM tables in the same database when you don't need the overhead.

Alot of people using databases don't need all those features - believe it or not, you can still get alot of useful work done without them. And frankly, mysql is alot more accesible then postgres for a database newb. That was my experience at least when I first picked it up - I tried to be elite and do the postgres thing, but it gave me problems and mysql was just easy as sin. I don't recall the specifics, it was a few years ago.

 

[Zugzwang152]

Originally posted by: Armitage

Originally posted by: Nothinman

They both have pros and cons.

MySQL's big cons are the facts that it's barely a database, it's not ACID compliant and it doesn't support a ton of things that 'real' databases do like stored procedures, views and sub queries.

MySQL has sub queries since 4.something, and I just got their newsletter today announcing a 5.0 beta with views, stored procedures and triggers. I believe that it is fully ACID compliant if you use the INNODB table type. Or use the faster MyISAM tables in the same database when you don't need the overhead.

Alot of people using databases don't need all those features - believe it or not, you can still get alot of useful work done without them. And frankly, mysql is alot more accesible then postgres for a database newb. That was my experience at least when I first picked it up - I tried to be elite and do the postgres thing, but it gave me problems and mysql was just easy as sin. I don't recall the specifics, it was a few years ago.

Agreed. For every person who needs advanced features, there's a hundred who just want a simple database to use or learn on. 🙂

 

[kamper]

Originally posted by: Armitage

Originally posted by: Nothinman

They both have pros and cons.

MySQL's big cons are the facts that it's barely a database, it's not ACID compliant and it doesn't support a ton of things that 'real' databases do like stored procedures, views and sub queries.

MySQL has sub queries since 4.something, and I just got their newsletter today announcing a 5.0 beta with views, stored procedures and triggers. I believe that it is fully ACID compliant if you use the INNODB table type. Or use the faster MyISAM tables in the same database when you don't need the overhead.

Alot of people using databases don't need all those features - believe it or not, you can still get alot of useful work done without them. And frankly, mysql is alot more accesible then postgres for a database newb. That was my experience at least when I first picked it up - I tried to be elite and do the postgres thing, but it gave me problems and mysql was just easy as sin. I don't recall the specifics, it was a few years ago.

I had the exact opposite experience. Mysql is not hard to get up and running but I always found programming with it to be a pain. There's so many little quirks and even though they're really expanding the functionality out now it always felt like they designed it only for extremely simple queries and they didn't really want you to do anything more. Postgres, on the other hand, has more functionality than I know what to do with. It seems like a lot of effort has been put into giving the developer as much power to do what he/she wants within the db itself. I've never had a need to push the performance limits of either so functionality is all I really care about.
I've got nothing against mysql and I hope it continues to get better and gains market share and all. It just feels so rigid compared to postgres.
Edit: I should mention that I'm used to working with SQL Server which has more features than either so that might explain why I don't enjoy mysql.

 

[Nothinman]

MySQL has sub queries since 4.something, and I just got their newsletter today announcing a 5.0 beta with views, stored procedures and triggers. I believe that it is fully ACID compliant if you use the INNODB table type. Or use the faster MyISAM tables in the same database when you don't need the overhead.

InnoDB was never the default, I thought I read that changed recently but I might be wrong. So technically your data isn't safe as long as you run MySQL MyISAM tables because they're not really ACID compliant. I actually made my list of "what MySQL doesn't have yet" from there "Features planned for 5.0" page.

Agreed. For every person who needs advanced features, there's a hundred who just want a simple database to use or learn on.

Learning on crap isn't a good idea. That's like telling all of the "What programming language should I start with?" thread starters to go out and grab VB3 because it's simple.

 

[Zugzwang152]

Learning on crap isn't a good idea. That's like telling all of the "What programming language should I start with?" thread starters to go out and grab VB3 because it's simple.

Heh, well Java is crap and is THE choice of colleges and universities for introductory programming. 😛

 

[CycloWizard]

Just curious - why use MySQL (or similar) instead of Access? I've done very little with SQL and only used Access, so I don't really know what the differences are.

 

[KLin]

Originally posted by: CycloWizard
Just curious - why use MySQL (or similar) instead of Access? I've done very little with SQL and only used Access, so I don't really know what the differences are.

MySQL is free to use. That's the main difference.

 

[WildHorse]

Originally posted by: Nothinman
MySQL's big cons are the facts that it's barely a database, it's not ACID compliant and it doesn't support a ton of things that 'real' databases do like stored procedures, views and sub queries.

Take a new look at what MySQL has evolved into over the last year or so. Look at current release 4.1.11 (or higher if you're reading this later). It's very good. You'll be pleasantly surprised.

Actually, MySQL does support subqueries. Your statement that it doesn't is about some old obsolete version. Stored procedures & views will be in v.5.

Market penetration of MySQL vs. Postgre is like comparing how many people use PCs vs. Apple computers. Regardless if you believe claims that Postgre might do one or two things better, the fact of life is....

. . .MySQL is what's happening!

 

[Nothinman]

Heh, well Java is crap and is THE choice of colleges and universities for introductory programming.

I dislike Java but it's not that bad, the language itself actually seems to be decent even though the runtimes suck.

Just curious - why use MySQL (or similar) instead of Access? I've done very little with SQL and only used Access, so I don't really know what the differences are.

Access is even worse than MySQL, at least use MSDE if you have to do database development on a MS product.

Take a new look at what MySQL has evolved into over the last year or so. Look at current release 4.1.11 (or higher if you're reading this later). It's very good. You'll be pleasantly surprised.

I really hate databases so I doubt I'll be pleasantly anything about it and the experiences we've had with it on Windows haven't been particularly fun.

Actually, MySQL does support subqueries. Your statement that it doesn't is about some old obsolete version. Stored procedures & views will be in v.5.

Again, 5 is only in beta and PostgreSQL has had them for a while now so why use an inferior product when PostgreSQL is just as free as MySQL?

Market penetration of MySQL vs. Postgre is like comparing how many people use PCs vs. Apple computers. Regardless if you believe claims that Postgre might do one or two things better, the fact of life is.... . . .MySQL is what's happening!

And McDonald's sells more fast food than any other chain, does that mean their food is the best? Not at all.

 

[Armitage]

Originally posted by: Nothinman

MySQL has sub queries since 4.something, and I just got their newsletter today announcing a 5.0 beta with views, stored procedures and triggers. I believe that it is fully ACID compliant if you use the INNODB table type. Or use the faster MyISAM tables in the same database when you don't need the overhead.

InnoDB was never the default, I thought I read that changed recently but I might be wrong. So technically your data isn't safe as long as you run MySQL MyISAM tables because they're not really ACID compliant.

AFAIK, ACID compliance is only important when doing transactions. If you are, you have the option to use the INNODB tables. My work to this point hasn't, so I use the faster MyISAM tables. Similarly for all the other missing features - subqueries are really the only thing that seems particularly useful to me, and it's easy enough to work around that in code if I need to.

I actually made my list of "what MySQL doesn't have yet" from there "Features planned for 5.0" page.

Agreed. For every person who needs advanced features, there's a hundred who just want a simple database to use or learn on.

Learning on crap isn't a good idea. That's like telling all of the "What programming language should I start with?" thread starters to go out and grab VB3 because it's simple.

Bad analogy IMHO - VB sucks at a much more fundemental level in the programming realm then MySQL does in the database realm 😛

If you want to make a programming language analogy, I'd say it's like C++ without STL - still very useful, and a complete programming language, but missing some pieces that could make things alot easier/better for some projects - maybe to the point of not being a good choice for those projects. And if you learn C++ without the STL, it's not a huge leap to move to a "real" C++ with an STL - the C++ you already know is still relavent & correct. If you need to move to a more complete RDBMS, the SQL you learned using MySQL is still valid - but now you have a few more tools in your box.

 

[Armitage]

Originally posted by: Nothinman
I really hate databases so I doubt I'll be pleasantly anything about it and the experiences we've had with it on Windows haven't been particularly fun.

I love em - I used to spend a good chunck of my time writing routines to extract & correlate data from vast collections of flat files generated by various sensors, models, simulations, etc. I decided there had to be a better way, learned enough SQL to be dangerous, and now all my simulations & tools use a database for nearly all of their IO and life has gotten much better 😛

 

[Armitage]

Originally posted by: kamper

Originally posted by: Armitage

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

I like the fact that you can pull numbers from them without inserting rows.

?? Not following you here at all ??
The whole point of autoincrement is to give inserted rows unique identifiers in ascending order. It sounds like postgres sequences are a different animal.

I personally found it much easier to use and the documentation on key generation was much better in general.

 

[kamper]

Originally posted by: Armitage

Originally posted by: kamper

Originally posted by: Armitage

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

I like the fact that you can pull numbers from them without inserting rows.

?? Not following you here at all ??
The whole point of autoincrement is to give inserted rows unique identifiers in ascending order. It sounds like postgres sequences are a different animal.

Creating a column with type "serial" (same as "autoincrement") creates another db object called a sequence. Everytime you insert null into that column it grabs the next number from the sequence and substitutes it. But you can also query the sequence directly to get a unique number. You can set up sequences that are not bound to a column and you can assign a serial column to a sequence that already exists (potentially many tables can draw from one sequence). It's not absolutely necessary but I find it quite comfortable to work with. I believe Oracle uses a very similar concept.

 

[Armitage]

Originally posted by: kamper

Originally posted by: Armitage

Originally posted by: kamper

Originally posted by: Armitage

Originally posted by: kamper
Now that this thread is wrapped up I'm going to totally nef on it and say that postgres sequences > mysql autoincrement 😉

Why?

I like the fact that you can pull numbers from them without inserting rows.

?? Not following you here at all ??
The whole point of autoincrement is to give inserted rows unique identifiers in ascending order. It sounds like postgres sequences are a different animal.

Creating a column with type "serial" (same as "autoincrement") creates another db object called a sequence. Everytime you insert null into that column it grabs the next number from the sequence and substitutes it. But you can also query the sequence directly to get a unique number. You can set up sequences that are not bound to a column and you can assign a serial column to a sequence that already exists (potentially many tables can draw from one sequence). It's not absolutely necessary but I find it quite comfortable to work with. I believe Oracle uses a very similar concept.

Interesting - not quite sure what I'd use something like that for, but interesting anyway.