Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

MySQL basics

by Anonymous Monk
on Sep 08, 2003 at 23:17 UTC ( #289903=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Can someone direct me to a good online tutorial or node that explains basic MySQL tutorials? I've never used a real database before and I think it's finally time to learn but all the tutorials/documents I've found weren't meant to be read by someone inexperienced in it.

On a scale from 1-10, how difficult/complicated is using this in comparison to SDBM or DB_File or the others?

Replies are listed 'Best First'.
Re: MySQL basics
by dbwiz (Curate) on Sep 08, 2003 at 23:32 UTC
Re: MySQL basics
by menolly (Hermit) on Sep 08, 2003 at 23:23 UTC

    Can you be more specific about what you're looking for?

    The MySQL website has plenty of information for installing, configuring and using MySQL. The MySQL Cookbook from O'Reilly is also a good reference.

    On the other hand, if someone else (like the sysadmin, if you're working on someone else's system) is handling that, and you just want to write Perl to interface with it, DBI tutorials are probably what you need.

      Yes, all I want to do is use perl with a MySQL db because people have been telling me it's a lot better than the other db's I've mentioned above. MySQL is already installed and setup on my server, I just need really simple tutorials to get me started using perl with it.
        MySQL is a popular database, but if you want more power, you should also look at PostgreSQL, which is a more complete and standard RDBMS. It'll take quite a while for MySQL to catch up to PostgreSQL, if ever. Might as well start with a real full-featured database.

        -- Randal L. Schwartz, Perl hacker
        Be sure to read my standard disclaimer if this is a reply.

Re: MySQL basics
by tachyon (Chancellor) on Sep 09, 2003 at 12:17 UTC
    A Short Guide to DBI by dominus is a good short sound intro.




      From the DBI documentation site.

      # ``A Short Guide to DBI''

      A new article written by Mark-Jason Dominus for the O'Reilly Perl site. This article is generally quite decent, if a little choppy in the reading. It does, however, contain errors, some of which really shouldn't be there. ( This is quite embarrassing considering O'Reilly never asked Tim nor Alligator ( the people they're paying to write the DBI book for them ) to check the article over ). It also doesn't bother listing the upcoming DBI book nor this WWW site in the References section!

        It was written in 1999 and predates the publication of Programming the Perl DBI by a year. Personally asking for referencing to vapourware (which was the case at the time) seems a bit much :-) Like all introductions it makes omissions of fact and glosses over areas. Perhaps you might enlighten us with the errors. There are certainly a lot less than in much of the SQL/DBI code we often see posted here.




Re: MySQL basics
by bradcathey (Prior) on Sep 09, 2003 at 12:40 UTC
    Agree with meryln and all the other mild criticism of MySQLs downsides, but I found that for doing dynamic web content, it's fine, easy to use, and readily available.

    So, depending on what you are doing, as far as books go, I found MySQL and Perl for the Web by Paul DuBois helpful, and if that is too heavy, try Larry Ullman's Quickstart Guide on MySQL (even has a discussion on normalization, etc.).

      Actually MySQL by Paul DuBois might be a great place to start... Talks more about the database than just the perl interface... (Insert holy wars here about mysql vs (favourite database)...
Re: MySQL basics
by tzz (Monk) on Sep 09, 2003 at 14:58 UTC
    The other comments point you to a lot of resources about MySQL, as requested. I would also recommend that you look at Class::DBI on CPAN. It's a great interface to quite a few databases, including Postgres and MySQL.


      These give you a CGI interface to a test database they have setup online to test your learning on. Many of the afformentioned links probably have a stronger set of material, but messing with a database that's not tied to a machine you own can be handy when you're first learning how NOT to blow things up in a db.

        Just wanted to second that. I found very useful, and totally "why didn't someone else think of this?" to be able to type code into the page and click on 'submit' and see what happened. If this is the way you like to learn then it's pretty good.

        ($_='kkvvttuubbooppuuiiffssqqffssmmiibbddllffss') =~y~b-v~a-z~s; print
Re: MySQL basics
by jonadab (Parson) on Sep 10, 2003 at 02:20 UTC

    Just a few minor tips, from someone who picked up DBI and MySQL earlier this year and is starting to get comfortable with it...

    • For any given database, you're going to have code in various places that needs to get a database handle, so write yourself a function that connects to the database and returns a handle. Throw this function in your own module (if you're comfortable with modules) or just in a that you can include (if you want to learn module-writing another time). This consolidates in one place not just the connect itself but also the various config info for the db in question (the MySQL username and so on). You'll save yourself a lot of copying and pasting.
    • Some of your code will call that function directly and use the handle to do various special things in SQL directly (create tables, get a read-lock for backup purposes, do a special query, whatever), but *most* of the time you will be doing one of four things: retrieving a specific record, adding a record, updating a record, or getting a list of records that match a certain value in a certain field. So write functions that do these four things, and throw them in your module or include file. This will save you yet more copying and pasting. Write these functions in such a way that they don't have to be modified to work with different tables, different databases.
    • Give every table in your database an id field that's NOT NULL PRIMARY KEY AUTO_INCREMENT so that you can pass this id around and know that it uniquely identifies a specific record within the table. Your function for getting a specific record can take this id number and the table name and return the record as a hashref (if you are reasonably comfortable with references) or just as a hash (if you want to deal with learning about references later). The function that creates a new record should assign NULL to this field so that it will be assigned automatically. DBD::mysql provides a way for you to get the id number that was assigned (see the documentation on, so your function can return that number if desired. Call this id field by the same name ("id" will do nicely) for every table, and save your memory for other stuff.
    • Your tables will change. You will add fields to them. I learned this the hard way. You do NOT want to change all your code every time you do that. (Trust me on this.) So, don't hardcode lists of fields, like I did at first. That way lies frustration. Instead, pass your records around as hashrefs (or as hashes, if you aren't comfortable with references just yet), and that way the code that's getting or setting one field doesn't have to concern itself with what other fields there are.

    $;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}} split//,".rekcah lreP rehtona tsuJ";$\=$ ;->();print$/
      jonadab, it sounds like you really need to look into Class::DBI or some other interface to the DBI. Your first two items and the last one are managed by Class::DBI automatically.

      As for the third suggestion, RDBMS table design is not all about auto incremented numeric primary keys. There are many cases where a string works better as the primary key, for instance. I would suggest a good RDBMS tutorial, but I haven't found any good ones :)


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://289903]
Approved by dbwiz
Front-paged by astaines
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (3)
As of 2018-02-21 21:54 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (288 votes). Check out past polls.