mysql « MemoSoup

mysql: rownum functionality

Here are some examples how to use “rownum” functionality (similar to Oracle) in mysql

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

rownum analog:

SELECT @rownum:=@rownum+1 rownum, mytable.*
FROM (SELECT @rownum:=0) [...]

mySQL: size of the database

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 [...]

MySQL: start SQL from shell script

The following code starts the SQL from the shell script directly, without additional SQL file

#!/bin/sh
–/. &> /dev/null; exec mysql "$@"

select ‘Hello, world!’ as test ;

The detailed explanation of the trick [...]

MemoSoup

Meta

mysql: rownum functionality

mySQL: size of the database

MySQL: start SQL from shell script