Copy this snippet, replace database_name with the name of your database AND utf8_general_ci to any other collation you preferred. This will generate the sql commands needed for changing collation of all tables. SELECT CONCAT(‘ALTER TABLE `’, t.`TABLE_SCHEMA`, ‘`.`’, t.`TABLE_NAME`, ‘`…
1. When that happen This error is issued when you try to alter a table in a way that it will break a foreign key constraint. Consider the following example CREATE TABLE test1(id INT NOT NULL PRIMARY KEY)ENGINE=INNODB; CREATE TABLE…
1. copy/insert from one table to another INSERT INTO `db_name`.`table_name`( `title`, `intro`, `content`, `keywords`, `description`) SELECT `title`, `intro`, `content`, `keywords`, `description`FROM `db_name`.`other_table`; 2. update whole column with the values from other…
# iso_country_list.sql # This will create and then populate a MySQL table with a list of the names and # ISO 3166 codes for countries in existence as of the date below. # For more about ISO 3166, see http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html…
# usps_states_list.sql# # This will create and then populate a MySQL table with a list of the names and # USPS abbreviations for US states and possessions in existence as of the date # below.# # For more about USPS…
MyISAM tables in MySQL have a maximum size of a row of 65,535 bytes, so all the data in a row must fit within that limit. However, the TEXT types are stored outside the table itself and only contribute 9…
UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) WHERE User=’root’; FLUSH PRIVILEGES;
1. Create user in MySQL databse CREATE USER ‘user’@'localhost’ IDENTIFIED BY ‘password’; 2. Set user rights GRANT ALL PRIVILEGES ON *.* TO ‘user’@'localhost’ WITH GRANT OPTION;
– time difference from now and one year SELECT DATEDIFF(DATE_ADD( NOW(),INTERVAL 1 YEAR), NOW()); – time difference from now and two weeks SELECT DATEDIFF(DATE_ADD(NOW(), INTERVAL 2 WEEK), NOW()); – time difference from now and five days SELECT DATEDIFF(DATE_ADD(NOW(), INTERVAL 5…
MySQL create view syntax CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] Create…