Public
snipts » mysql 
showing 1-20 of 199 snipts for mysql
-
∞ mysqldump -- dump a table with specified conditions
mysqldump -- dump a table with specified conditions you can use where clause to specified some conditions to dump a table, e.g. : mysqldump --no-create-db=TRUE --no-create-info=TRUE --add-drop-table=FALSE --default-character-set="gbk" --where="id between 1 and 1000" bbs bbs_post>bbs_post.sql
-
∞ number of records in recordset
$result = mysql_query($query) or die(mysql_error()); $count = mysql_num_rows($result); echo "$count";
-
∞ Set a WP pass from mysql shell
UPDATE wp_users SET user_pass = MD5('newpassword') WHERE user_login = "admin";
-
∞ install mysql gem
$ sudo env ARCHFLAGS="-arch x86_64" gem install mysql -- --with-mysql-config=/opt/local/bin/mysql_config5
-
∞ install mysql with macports
$ sudo port install mysql5-devel $ sudo /opt/local/lib/mysql5/mysql_install_db -user=mysql
-
∞ install mysql 64-bit
$ sudo env ARCHFLAGS="-arch x86_64" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config
-
∞ Installing mysql gem on snow leopard
$ sudo env ARCHFLAGS="-arch x86_64" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config $ sudo port install mysql5-devel $ sudo /opt/local/lib/mysql5/mysql_install_db -user=mysql $ sudo env ARCHFLAGS="-arch x86_64" gem install mysql -- --with-mysql-config=/opt/local/bin/mysql_config5
-
∞ Get all enum values from MySQL database
/** * * @return array */ function get_enum_values() { $enum_result = array(); $sql = "SHOW COLUMNS FROM {$tablename} "; $rs = $adodb->Execute($sql); if ( ! $rs ) { set_error($adodb->ErrorMsg()); return FALSE; } if ( $rs->RecordCount() > 0 ) { while ( $row = $rs->FetchRow() ) { if ( ereg(('set|enum'), $row['Type']) ) { eval(ereg_replace('set|enum', '$'.$row['Field'].' = array', $row['Type']).';'); $enum_result[strtolower($row['Field'])] = array_combine($$row['Field'], $$row['Field']); } } } return $enum_result; }
-
∞ Set MySQL to use UTF-8 and convert database
##/etc/my.cnf: # [mysql] # default-character-set=utf8 # [mysqld] # character-set-server=utf8 # collation-server=utf8_general_ci # [client] # default-character-set=utf8 /etc/init.d/mysqld stop /etc/init.d/mysqld start # dump db mysqldump -u dbuser -pdbpass --opt --skip-set-charset --default-character-set=latin1 --skip-extended-insert databasename > database.table.sql # replace charset perl -i -pe 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' database.table.sql # import db cat database.table.sql | mysql -u dbuser -pdbpass databasename
-
∞ database intensive recommendations
-- database intensive way of recommending uris based on a neighbour -- the list 'barack_obama', 'stephen_fry' represents a user's current uris select c.uri, count(c.uri) as popularity from consumption c, ( select c.user, count(c.user) as total from consumption c where c.uri in ('barack_obama','stephen_fry') group by c.user having total = ( select count(c.user) as total from consumption c where c.uri in ('barack_obama','stephen_fry') group by c.user order by total desc limit ) ) p where p.user = c.user and c.uri not in ('barack_obama','stephen_fry') group by c.uri order by count(c.uri) desc limit 100;
-
∞ create DB utf8
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
-
∞ mass insert test value into a table
DELIMITER $$ DROP PROCEDURE IF EXISTS autoInsert$$ CREATE PROCEDURE autoInsert(tableName varchar(100), rows int) BEGIN DECLARE i INT DEFAULT 1; -- repeat one million times -- WHILE(i < rows) DO INSERT INTO tableName(work_name, email, create_time) VALUES ('testPosition for a special company','test@test.com', now()); SET i = i+1; END WHILE; END$$ DELIMITER ; OR DELIMITER $$ DROP PROCEDURE IF EXISTS autoInsert $$ CREATE PROCEDURE autoInsert(tableName varchar(100), rows int) BEGIN DECLARE counter INT DEFAULT 1; simple_loop: LOOP INSERT INTO tableName(work_name, email, create_time) VALUES ('testPosition for a special company','test@test.com', now()); SET counter = counter + 1; IF counter = rows THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$ DELIMITER ;
-
∞ Truncate Cache tables
TRUNCATE `cache`; TRUNCATE `cache_block`; TRUNCATE `cache_content`; TRUNCATE `cache_filter`; TRUNCATE `cache_form`; TRUNCATE `cache_menu`; TRUNCATE `cache_page`; TRUNCATE `cache_update`; TRUNCATE `cache_views`; TRUNCATE `cache_views_data`; TRUNCATE `search_dataset`; TRUNCATE `search_index`; TRUNCATE `search_node_links`; TRUNCATE `search_total`; TRUNCATE `views_object_cache`; TRUNCATE `watchdog`;
-
∞ Copy an existing MySQL table to a new table
# METHOD 1 (Tables are exactly equal) mysql> CREATE TABLE update_control LIKE smsc.rrd_epoch; mysql> INSERT recipes_new SELECT * FROM production.recipes; # METHOD 2 (Tables have only some columns equal) mysql> INSERT update_control (date,epoch,rrd_updated,date_rrd_updt) SELECT * FROM smsc.rrd_epoch;
-
∞ Different methods to create users for MySQL BBDD
## First Method mysql> GRANT ALL ON bbdd.* TO my_user@'%' IDENTIFIED BY 'my_pasword'; Query OK, 0 rows affected (0.07 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.05 sec) ## Second Method mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER ON bbdd.* TO my_user@localhost IDENTIFIED BY 'yourpassword'; mysql> quit # mysqladmin -p reload
-
∞ Get the next autoincrement number
SELECT Auto_increment as thenext FROM information_schema.tables WHERE table_name='thetablename'
-
∞ Count Monthly/Yearly Totals in MySQL
SELECT MONTHNAME(createDt) AS month, YEAR(createDt) AS year, COUNT(*) AS total FROM tracking GROUP BY MONTH(createDt), YEAR(createDt) ORDER BY id
-
∞ Use found_rows() in MySQL
// use found_rows() as a sql function mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS();
-
∞ mysql process list
mysqladmin -p processlist
-
∞ MySQL Stored Procedure Note
MySQL Stored Procedure Note # Integration with SQL example create procedure example() begin declare l_book_count integer; select count(*) into l_book_count from books where author like '%HARRISON,GUY%'; select concat('Guy has written (or co-written) ', l_book_count, ' books.'); -- oh, and I changed my name, so... -- update books set author = replace(author, 'GUY', 'GUILLERMO') where author like '%HARRISON,GUY%'; end # Control and Conditional Logic * IF and CASE statements * A full complement of looping and iterative controls # example: Stored procedure with control and conditional logic CREATE PROCEDURE pay_out_balance(account_id_in INT) BEGIN DECLARE l_balance_ramaining NUMERIC(10, 2); payout_loop:LOOP -- accout_balance is another stored procedure, MySQL allows you to call a stored procedure from within another stored procedure -- SET l_balance_ramaining = accout_balance(account_id_in); IF l_balance_ramaining < 1000 THEN LEAVE payout_loop; ELSE CALL apply_balance(account_id_in, l_balance_ramaining); END IF; END LOOP; END # Stored Functions A stored function is a stored program that returns a single value and that can be used whenever a built-in function can be used for example. -- A stored function to calculate age from date of birth -- CREATE FUNCTION f_age(in_dob datetime) returns int NO SQL BEGIN DECLARE l_age INT; IF DATE FORMAT(NOW(), '00-%m-%d') >= DATE_FORMAT(in_dob, '00-%m-%d') THEN -- This person has had a birthday this year -- SET l_age = DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(in_dob, '%Y'); ELSE -- Yet to have a birthday this year SET l_age = DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(in_dob, '%Y) - 1; END IF; RETURN (l_age); END; # When things go wrong, Error handling in a stored program CREATE PROCEDURE sp_product_code(in_product_code VARCHAR(2), in_product_name VARCHAR(30)) BEGIN DECLARE l_dupkey_indicator INT DEFAULT 0; -- Define a named condition, duplicate_key, that is associated with MySQL error 1062. -- DECLARE duplicate_key CONDITION FOR 1062; -- Ensure that it gets set to a valueof 1(true) only if a duplicate key violation takes place -- DECLARE CONTINUE HANDLER FOR duplicate_key SET l_dupkey_indicator = 1; -- Insert a new product with the user-provided code and name. -- INSERT INTO product_codes(product_code, product_name) VALUES (in_product_code, in_product_name); -- If there has been a duplicate key violation, run the UPDATE statement to change the name of the product with the specified code. -- IF l_dupkey_indicator THEN UPDATE product_codes SET product_name = in_product_name WHERE product_code = in_product_code; END IF; END # Triggers # example: Trigger to maintain a derived column value create trigger employees_trg_bu -- the trigger code will execute prior to an UPDATE statement on the employees table -- BEFORE UPDATE ON employees -- FOR EACH ROW indicates that the trigger code will be executed once for each row being affected by the DML statement. -- FOR EACH ROW BEGIN IF NEW.salary < 50000 THEN SET NEW.contrib_401K = 500; ELSE SET NEW.contrib_401K = 500 + (NEW.salary - 50000)*.01; END IF; END # define variables # DECLARE: A statement used to create local variables for use in the stored program. # SET: A statement used to assign a value to a variable. DELIMITER $$ DROP PROCEDURE IF EXISTS variable_demo$$ CREATE PROCEDURE variable_demo() BEGIN DECLARE my_integer INT; DECLARE my_big_integer BIGINT; DECLARE my_currency NUMERIC(8, 2); DECLARE my_pi FLOAT DEFAULT 3.1415926; DECLARE my_text TEXT; DECLARE my_dob DATE DEFAULT '1982-01-01'; DECLARE my_varchar VARCHAR(30) DEFAULT "Hello World!"; SET my_integer = 20; SET my_big_integer = POWER(my_integer, 3); END$$ DELIMITER ; # Parameter Modes(IN, OUT, INOUT) # IN: This mode is the default. It indicates that the parameter can be passed into the stored procedure but that any modifications are not returned to the calling program. # OUT: This mode means that the stored procedure can assign a value to the parameter, and that value will be passed back to the calling program. # INOUT: This mode means that the stored procedure can read the parameter and that the calling procedure can see any modifications that the stored procedure may make to that parameter. DELIMITER $$ DROP PROCEDURE if EXISTS my_sqrt$$ CREATE PROCEDURE my_sqrt(input_number INT, OUT out_number FLOAT) BEGIN SET out_number = SQRT(input_number); END$$ DELIMITER ; -- use this procedure -- CALL my_sqrt(15, @out_value); select @out_value; # Conditional Execution # example: Creating and executing a stored procedure that contains an IF statement DELIMITER $$ DROP PROCEDURE IF EXISTS discounted_price$$ CREATE PROCEDURE discounted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2)) NO SQL BEGIN IF (normal_price > 500) THEN SET discount_price = normal_price * .8; ELSEIF (normal_price > 100) THEN SET discount_price = normal_price * .9; ELSE SET discount_price = normal_price; END IF; END$$ DELIMITER ; CALL discounted_price(150, @discount_price); select @discount_price; # Loops # Loops allow stored procedure to execute statements repetitively. The MySQL stored program language offers three types of loops: # * Simple loops using the LOOP and END LOOP clauses. # * Loops that continue while a condition is true, using the WHILE and END WHILE clauses. # * Loops that continue until a condition is true, using the REPEAT and UNTIL clauses. # With all three loop types, you terminate execution of the loop with the LEAVE statements. # example: A simple loop inside a stored procedure DELIMITER $$ CREATE PROCEDURE simple_loop() BEGIN DECLARE counter INT DEFAULT 0; simple_loop: LOOP SET counter = counter + 1; IF counter = 10 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; SELECT "I can count to 10" as 'test'; END$$ DELIMITER ; CALL simple_loop(); # Interacting with the Database # SELECTing INTO Local Variables # example: A stored procedure with an embedded SELECT INTO statement DELIMITER $$ DROP PROCEDURE IF EXISTS customer_sales$$ CREATE PROCEDURE customer_sales(in_customer_id INT) READS SQL DATA BEGIN DECLARE total_sales NUMERIC(10, 2); SELECT SUM(SALE_VALUE) INTO total_sales FROM sales WHERE CUSTOMER_ID = in_customer_id; SELECT CONCAT("Total sales for ", in_customer_id, " is ", total_sales); END$$ DELIMITER ; # Using Cursors # SELECT INTO is fine for single-row queries, but many applications require the querying of multiple rows of data. You will use a cursor in MySQL to accomplish this. A cursor lets you fetch one or more rows from a SQL result set into stored program variables, usually with the intention of performing some row-by-row processing on the result set. DELIMITER $$ DROP PROCEDURE IF EXISTS cursor_example$$ CREATE PROCEDURE cursor_example() READS SQL DATA BEGIN DECLARE l_employee_id INT; DECLARE l_salary NUMERIC(10, 2); DECLARE l_department_id INT; -- lets us know when all the rows have been retrieved from the result set. -- DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees; -- Declare a "handler" that defines the actions we will take when no more rows can be retrieved from a SELECT statement. -- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; emp_loop: LOOP FETCH cur1 INTO l_employee_id, l_salary, l_department_id; IF done = 1 THEN LEAVE emp_loop; END IF; END LOOP emp_loop; CLOSE cur1; END$$ DELIMITER ; -- 2.8. Returning Result Sets from Stored Procedures -- # example: An unbounded SELECT statement in a stored procedure DELIMITER $$ DROP PROCEDURE IF EXISTS sp_emps_in_dept$$ CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT) BEGIN SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees WHERE department_id = in_employee_id END$$ DELIMITER ; # example: Embedding Non-SELECTs DELIMITER $$ DROP PROCEDURE IF EXISTS sp_update_salary$$ CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(10, 2)) BEGIN IF in_new_salary < 5000 OR in_new_salary > 500000 THEN SELECT 'Illegal salary; salary must be between $5,000 and $500,000'; ELSE UPDATE employees SET salary = in_new_salary WHERE employee_id = in_employee_id; END IF; END$$ DELIMITER ; # example: Calling Stored Programs from Stored Programs DELIMITER $$ DROP PROCEDURE IF EXISTS call_example$$ CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20)) NO SQL BEGIN DECLARE l_bonus_amount NUMERIC(10, 2); -- If he is a manager, we call the calc_manager_bonus stored procedure; if he is not a manager, we call the calc_minion_bonus stored procedure. -- IF employee_type = 'MANAGER' THEN CALL calc_manager_bonus(employee_id, l_bonus_amount); ELSE CALL calc_minion_bonus(employee_id, l_bonus_amount); END IF; -- Call the grant_bonus stored procedure that passes as arguments the employee_id and the bonus amount. -- CALL grant_bonus(employee_id, l_bonus_amount); END$$ DELIMITER ; # example: A more complex stored procedure DELIMITER $$ CREATE PROCEDURE putting_it_all_together(in_department_id INT) MODIFIES SQL DATA BEGIN DECLARE l_employee_id INT; DECLARE l_salary NUMERIC(10, 2); DECLARE l_department_id INT; DECLARE l_new_salary NUMERIC(10, 2); DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees WHERE department_id = in_department_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises(employee_id INT, department_id INT, new_salary NUMERIC(10, 2)); OPEN cur1; emp_loop: LOOP FETCH cur1 INTO l_employee_id, l_salary, l_department_id; IF done=1 THEN /* No more rows */ LEAVE emp_loop; END IF; CALL new_salary(l_employee_id, l_new_salary); /* get new salary */ IF (l_new_salary <> l_salary) THEN /* Salary changed */ UPDATE employees SET salary = l_new_salary WHERE employee_id = l_employee_id; /* Keep track of changed salaries */ INSERT INTO emp_raises(employee_id, department_id, new_salary) VALUES (l_employee_id, l_department_id, l_new_salary); END IF; END LOOP emp_loop; CLOSE cur1; /* Print out the changed salaries */ SELECT employee_id, department_id, new_salary FROM emp_raises ORDER BY employee_id; END$$ DELIMITER ; -- 2.11. Stored Functions --
MySQL in a Nutshell