mysql

MySQL timestamp - how to default to "now"

Here's a quick example of how to default a MySQL timestamp field to "now", i.e., the current date and time. The following MySQL table definition shows a field named date_created that uses the now function to record the timestamp for when the row was created:

By Alvin Alexander on February 21, 2013

The beginning of a MySQL database script

Here is some code that I use at the beginning of a MySQL database script to (a) create a database, (b) create a local user to access that database ('foo_user'@'localhost'), (c) create a remote user that can access the database ('foo_user'@'%'), and (d) then use that database (which I need to do before starting a bunch of CREATE TABLE statements):

By Alvin Alexander on August 6, 2011

How I fixed my JDBC MySQL transaction problem

A JDBC MySQL transactions tip: If your transactions aren't working with your new MySQL database you may have the same problem I just had. I created my database tables with the default MySQL storage engine (MyISAM), and guess what? MyISAM doesn't support transactions.

A cool thing about MySQL is that it supports multiple database engines (storage engines), so to fix the problem where my transactions weren't working, all I had to do was specify a MySQL storage engine that does support MySQL transactions, in this case the MySQL InnoDB storage engine.

By Alvin Alexander on January 14, 2011

Show MySQL foreign keys with the "show create table" command

I got a really brutal looking error message from Spring and MySQL yesterday. I've been working on a Java-based web interface for Nagios for a client, and I ran into an error message that basically says "Cannot add or update a child row: a foreign key constraint fails". If you like ugly, gruesome error messages here is the full-blown error:

By Alvin Alexander on August 31, 2011

How to get the generated key from a MySQL auto_increment field after an INSERT

Here's a quick look at how to get the generated key from a MySQL database table after performing a SQL INSERT statement on a table that has an auto_increment field. (Some databases also refer to this as an identity field.)

To get the generated key from a MySQL database table, just use the MySQL LAST_INSERT_ID function, calling it as shown below, immediately after performing your INSERT command (and very importantly, also using the same connection):

By Alvin Alexander on August 6, 2011

How to perform a case-insensitive database query

Database FAQ: How do I do a case-insensitive database search?

Answer: When performing a database search using MySQL, PostgreSQl, SQL Server, Oracle, etc., I convert the results of the query to uppercase using the UPPER function, and make sure my search parameters are also in uppercase. Here's an example of how I do this when searching on the first_name field of an Employees database table:

By Alvin Alexander on August 6, 2011

Database-driven version of states dropdown data

Some time ago I created a brief blog entry that shows an HTML select/option block for displaying all the states in the United States in a dropdown list (combo box). After a few requests I'm now providing that same information here in a database table format.

By Alvin Alexander on May 20, 2008

MySQL ERROR 1005 (HY000): Can't create table

I got the following MySQL error ("1005 error") earlier today when trying to create a table with a foreign key relationship:

By Alvin Alexander on August 8, 2011

MySQL CSV import example

I just ran into a situation where I needed to import some CSV data into a MySQL database table. I already had the data I needed in a CSV file format, and I needed to import the data in that file into my MySQL database table.

A few more specifics about the problem and the solution:

By Alvin Alexander on August 30, 2011

MySQL example database - The Coffee Break Database from Sun's JDBC tutorial

MySQL database FAQ: Can you share an example MySQL database schema (i.e., a MySQL database create script).

For what I do I often need a sample database. For my current writing I decided to use the "coffeebreak" database defined in Sun's JDBC tutorial. If it saves you any time in your work, here are the commands. I've only tested these with MySQL, but they should work with most other databases.

Before worrying about the schema, here are three steps that you'll want to do first:

By Alvin Alexander on May 8, 2013

new

categories