MySQL : Java Glossary

2008-03-29 by Roedy Green ©1996-2008 Canadian Mind Products
index page for letter ⇒ punctuation 0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z (all)

MySQL

A popular open source database. It started out as a toy but has matured into a full fledged SQL database engine. It runs as a windows service in the background, automatically starting up with you boot. Sun bought it out on 2008-02-28.

It even supports full text searching, the ability to find records that contain a word anywhere in the key. Simply declare an index of type FULLTEXT on the TEXT/CHAR/VARCHAR table columns which contains the text you want to search, then query the table using the "MATCH()… AGAINST" syntax. There’s a manual section which describes this facility.

MySQL follows the Unix tradition with command line utilities with a plethora of options and the documentation that treats them all as equally important. There is also a GUI-style database administrator available as a separate download. It lets you create new users and do the configuring with checkboxes instead of ini files.

Part of the configuration is to decide on a single encoding for all databases, e.g. UTF-8 for multilingual, or perhaps Latin-1 for English only.

The MySQL users have nothing to do with operating system users. MySQL manages its own list of users and passwords stored encrypted in the database tables. The administrator is called root.

Gotchas

Its error message are hopeless. They give you no clue either where the problem is or what the problem is. All they do is point you to a CREATE TABLE and say there is something wrong somewhere in that page of SQL.
MySQL has a type called TIMESTAMP, but it is quite different from the standard TIMESTAMP. It’s a magic data type with side effects in that it’s automatically updated to the current date and time if some criteria are fulfilled.
MySQL has a type called DATETIME. Like MySQL’s TIMESTAMP type, it stores a combination of date and time without fractional seconds. There are no side effects associated with the DATETIME type — which makes it the closest match to the SQL standard’s TIMESTAMP type.
To get a standard Java long timestamp = System.currentTimeMillis(), you would have to store a primitive long.
One curious thing is that the maxmimum length for a VARCHAR seems to depend on what else there is potentially in the record. I think MySQL wants every record to be under 64K in total so that the fields can be addressed with 16-bit offsets.

Key Utilities

Key MySQL Utilities
Utility	Use
mysql	Enter SQL commands manually to build tables or do ad hoc queries and updates. Invoke with mysql -hlocalhost -uroot -psesame to define the host (possibly remote), user id, and password. Note the lack of a space after -h, -u and -p. You can then type in commands like these: /* display version of MySQL you are using / SELECT VERSION(); / display all databases */ SHOW DATABASES; SQL: for more commands you can enter to explore You can run SQL command from a text file like this: mysql -hlocalhost -uroot -psesame < mycommands.sql
mysqlD	The database engine itself. You can start it from the command line, though normally you start and stop it as a service. There are several versions of it, with various functions stripped out for speed.
mysqlAdmin	Database adminstration, e.g. clear the grant tables, by command line. Invoke with mysqlAdmin -hlocalhost -uroot -psesame
mysqlAdministrator	a GUI-style database administrator available as a separate download. It lets you create new uses and do the configuring with checkboxes instead of ini files. It is quite a bit easier to use than the command line version. It lives in its own directory, not in bin with the rest.
mysqlImport	Used to bulk load text data into the database.
mysqlInstanceConfig	configure the my.ini file with a GUI.
mysqlShow	Displays a list of databases available. Invoke with mysqlShow -hlocalhost -uroot -psesame You can also use this command to display a list of available tables: mysqlShow -hlocalhost -uroot -psesame alphadatabase or a list of column names in a particular table mysqlShow -hlocalhost -uroot -psesame alphadatabase peopletable or a list of values in a particular column mysqlShow -hlocalhost -uroot -psesame alphadatabase peopletable birthdate

Books

recommend book⇒MySQL Cookbook

paperback

hardcover

ISBN13:

ISBN10:

publisher:

O’Reilly

published:

2006-11-27

by:

Paul Dubois

Covers Java, C, Perl and PHP.

	abe books.co.uk	abe books.ca
	amazon.co.uk	amazon.ca
	abe books.de	chapters.indigo.ca
	amazon.de	abe books.com
	abe books.fr	amazon.com
	amazon.fr	barnes and noble.com
	abe books.it	powells.com
	iberlibro.com	o’reilly safari
		abe books anz

recommend book⇒MySQL (3rd Edition)

paperback

ISBN13:

978-0-672-32673-8

ISBN10:

0-672-32673-6

publisher:

Sams

published:

2005-03-18

by:

Paul DuBois

1320 pages. Aimed at the beginner who knows nothing of SQL or MySQL. It is aimed at the C, Perl or PHP programmer. It does not discuss Java or JDBC.

	abe books.co.uk	abe books.ca
	amazon.co.uk	amazon.ca
	abe books.de	chapters.indigo.ca
	amazon.de	abe books.com
	abe books.fr	amazon.com
	amazon.fr	barnes and noble.com
	abe books.it	powells.com
	iberlibro.com	abe books anz

service
SQL
SQL vendors

	Please email your feedback for publication, errors, omissions, broken/redirected link reports and suggestions to improve this page to Roedy Green :
	Canadian Mind Products
	mindprod.com IP:[65.110.21.43]
	Your face IP:[38.103.63.61]	The information on this page is for non-military use only.
	You are visitor number 4,862.	Military use includes use by defence contractors.
	You can get a fresh copy of this page from:	or possibly from your local J: drive (Java virtual drive/mindprod.com website mirror)
	http://mindprod.com/jgloss/mysql.html	J:\mindprod\jgloss\mysql.html