In Mysql, if you want to either updates or inserts a row in a table, depending if the table already has a row that matches the data, you can use “ON DUPLICATE KEY UPDATE”. INSERT INTO employees(id, name, hire_date ) VALUES (1, 'John', '2016-11-29' ) ON DUPLICATE KEY UPDATE ... Read more
Category Archives: Mysql
Some ETL (Extract-Transform-Load) tools for MySQL
Withdata Software provide some ETL (Extract-Transform-Load) tools for MySQL: FileToDB Load TXT, CSV, TSV, XML, JSON, Excel, SQL, RDF, INI data to MySQL DBToFile Export MySQL data to TXT, CSV, TSV, XML, JSON, Excel, SQL files DBCopier Copy data between MySQL and other rational ... Read more
Some data import / export tools for Percona
Withdata Software provide some tools for MySQL, all these tools can work for Percona: See how to use these tools work for Percona: Import XML data into Percona (MySQL) Schedule and automate Percona (MySQL) importing XML data task Import data into Percona (MySQL) from MongoDB exported JSON ... Read more
Some data import / export tools for MariaDB
Withdata Software provide some tools for MySQL, all these tools can work for MariaDB: See how to use these tools work for MariaDB: Import XML data into MariaDB (MySQL) Schedule and automate MariaDB (MySQL) importing XML data task Import data into MariaDB (MySQL) from MongoDB exported JSON ... Read more
How to get index column names of a table from MySQL
SELECT INDEX_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'my_schema_name' AND TABLE_NAME = 'my_table_name' See also: How to get index column names of a table from DB2 How to get index column names of a table from SQL Server How to get index column names of a table ... Read more
How to get primary key of a table from MySQL
select COLUMN_NAME,INDEX_NAME from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA = 'my_schema_name' and TABLE_NAME='my_table_name' and INDEX_NAME='PRIMARY' See also: How to get primary key of a table from PostgreSQL How to get primary key of a table from SQLite How to get primary key of a ... Read more
How to execute sql file at MySQL command line
If you’re at the MySQL command line mysql> you can declare the SQL file as source, like: mysql> source 'path/to/test.sql'; Or use the MySQL command line client: mysql -hhostname -uuser database < path/to/test.sql ... Read more
How to limit the number of rows returned by an MySQL query
In MySQL, you can use “LIMIT <skip> <count>”, like this: select * from sometable order by name limit 20,10 In Oracle: http://www.withdata.com/ad/oracle/how-to-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering.html . In SQL ... Read more
Change MySQL table character set from latin1 to utf8
I have a table “t_user”, it’s character set is “latin1” when create it, and I want to save some unicode text at field “username”, but when I saved, the data is “????”, so I need to change character set from “latin1” to ... Read more
Fetch the row which has the Max value for a column on Mysql
I have a MySQL table clientipstat, 3 columns: barcode, statdate, count. I want to get the latest row for each barcode(1 result per unique barcode), like ... Read more