How to Find and Replace Text in MySQL March 25, 2009

MySQL database has a simple string function REPLACE() that allows table data with the matching string (from) to be replaced by new string.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

example:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

Mysql+Date functions January 29, 2009

Example’s that uses date functions.

The following query selects all rows with a date_col value from within the last 30 days:
mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)
SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
-> ‘2008-02-02′
SELECT ADDDATE(‘2008-01-02′, INTERVAL 31 DAY);
-> ‘2008-02-02′
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> ‘Sunday October 2009′
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> ‘22:23:00′
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',

-> '%D %y %a %d %m %b %j');
-> ‘4th 00 Thu 04 10 Oct 277′
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');

-> ‘22 22 10 10:23:00 PM 22:23:00 00 6′
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> ‘1998 52′
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> ‘00′
mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
-> 2009
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03');
-> 200907
mysql> SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03');

-> 20102
mysql> SELECT EXTRACT(MICROSECOND
-> FROM '2003-01-02 10:30:00.000123');
-> 123

how to find the last insert id in java+mysql? January 23, 2009

JDBC 3.0 (MySQL is has a JDBC 3 compliant driver) method as such:

Statement stat = dbConn.createStatement();
Stat.executeUpdate(“Some insert statement here”);

ResultSet rs = stat.getGeneratedKeys();
Int insertedKeyValue = rs.getInt(1);

Rs.close();
Stat.close();

Optimize All Tables In A MySQL Database October 22, 2008

<?php
//Database connection
$alltbls = mysql_query("SHOW TABLES");
while ($table = mysql_fetch_assoc($alltbls))
{
   foreach ($table as $db => $tablename)
   {
      mysql_query("OPTIMIZE TABLE '".$tablename."'")
      or die(mysql_error());
   }
}
?>

How to calculate the difference between two dates October 17, 2008

long diffInMilleseconds = end.getTime() – start.getTime();

long noofdays=diffInMilleseconds/86400000;

long diffInSeconds = diffInMilleseconds/1000;
long diffInMinutes = diffInSeconds/60;
long diffInHours = diffInMinutes/60;
long diffInDays = diffInHours/24;

Memory Problems with Mysql Connector August 23, 2008

GOOD:

mysql.jar (221 kb, 10/7/2003)
mysql-connector-java-3.0.16-ga-bin.jar (231 kb, 11/16/2004)
mysql-connector-java-3.0.17-ga-bin.jar (241 kb, 6/22/2005)

BAD (memory leak):

mysql-connector-java-3.1.10-bin.jar (409 kb, 6/23/2005)
mysql-connector-java-3.1.14-bin.jar (449 kb, 10/18/2006)
… (other versions)
mysql-connector-java-5.1.6-bin.jar (687 KB, 3/5/2008)

So – does anyone know what the major change was between 3.0.17 and 3.1.10 that would have such a dramatic effect?

Common SQL Commands May 28, 2008

SQL commands are divided into categories, the two main ones being Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands. DML commands deal with data, either retrieving it or modifying it to keep it up-to-date. DDL commands create or change tables and other database objects such as views and indexes.

A list of the more common DML commands follows:

• SELECT — used to query and display data from a database. The SELECT statement specifies which columns to include in the result set. The vast majority of the SQL commands used in applications are SELECT statements.
• INSERT — adds new rows to a table. INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.
• DELETE — removes a specified row or set of rows from a table
• UPDATE — changes an existing value in a column or group of columns in a table

The more common DDL commands follow:

• CREATE TABLE — creates a table with the column names the user provides. The user also needs to specify a type for the data in each column. Data types vary from one RDBMS to another, so a user might need to use metadata to establish the data types used by a particular database. CREATE TABLE is normally used less often than the data manipulation commands because a table is created only once, whereas adding or deleting rows or changing individual values generally occurs more frequently.
• DROP TABLE — deletes all rows and removes the table definition from the database. A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level. However, support for the CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.
• ALTER TABLE — adds or removes a column from a table. It also adds or drops table constraints and alters column attributes

MySQL Full-text Searching May 20, 2008

Intended Audience

This tutorial is intended for developers using MySQL
(http://www.MySQL.com/) and PHP
(http://www.php.net) who want to create a
searchable database of some sort of textual data. It will focus on the
Full-text capabilities presented by MySQL, moving into the Boolean opportunities
that are presented in the latest alpha version, 4.1, of MySQL.

Overview

Using directories to group articles by category is a great way
to help people to navigate through many articles. At some point, however,
someone will want to find all the articles that pertain to a certain
topic that may not have a directory of it’s own, or may span many
directories . This is what the search engine is for.

Read more