MySQL

mysql: rownum functionality

Thursday, January 31st, 2008

Select, update etc…

UPDATE mytable
SET col1 = ’somevalue’
ORDER BY col2
LIMIT 300

rownum analog:

SELECT @rownum:=@rownum+1 rownum, mytable.*
FROM (SELECT @rownum:=0) r, mytable;

mySQL: size of the database

Wednesday, January 23rd, 2008

SELECT table_schema “Database”,
 sum( data_length + index_length ) / 1024 / 1024 “Size (MB)”,
 sum( data_free )/ 1024 / 1024 “Free (MB)”
FROM information_schema.TABLES
GROUP BY table_schema ;

This will work in mySQL 5.0.2 and newer. Use SHOW TABLE STATUS command for other versions.

MySQL: start SQL from shell script

Monday, March 19th, 2007

The following code starts the SQL from the shell script directly, without additional SQL file
#!/bin/sh
–/. &> /dev/null; exec mysql “$@”
The detailed explanation of the trick is here

Automatic MySQL backup script

Wednesday, March 15th, 2006

#!/bin/bash
#
# MySQL Backup Script
# VER. 2.5 - http://sourceforge.net/projects/automysqlbackup/
# Copyright (c) 2002-2003 wipe_out@lycos.co.uk
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program [...]

MySQL 5.x: passing limits as stored procedure / function paramters

Wednesday, February 1st, 2006

MySQL 5.x (at least 5.0.15 and earlier versions) does not allow using variables or procedure formal parameters with LIMIT. Here is a workaround:

CREATE PROCEDURE sp (
  IN LimitStart_ INT,
  IN LimitCnt_ INT
)
BEGIN
  SET @lim = CONCAT(’ LIMIT ‘, LimitStart_, ‘,’, LimitCnt_);
  SET @q = “SELECT mycol FROM mytable”;
  SET @q = CONCAT(@q, @lim);
  PREPARE st [...]

Connecting to MySQL database via PHP PEAR DB

Monday, January 2nd, 2006

<strong>require_once</strong>(”DB.php”);
$dbType = “mysql”;
$dbUser = “admin”;
$dbPass = “password”;
$dbServer = “localhost”;
$dbName = “mysql”;

$db = DB::connect(”$dbType://$dbUser:$dbPass@$dbServer/$dbName”);
if(DB::isError($db))
{
<blockquote>die(”Couldn’t connect to database”);</blockquote>
}
else
{
 $query = “SELECT firstname, lastname FROM users”;
    $uResult = $db->query($query);
    while ($uRow = $uResult->fetchRow())
    {     
echo $uRow[0] . ” => ” . $uRow[1] . “\n”;
    }
    $db->disconnect();
}

Keep on coding