MySql: Insert a row and get the content
Execute your insert statement and then you can do this: SELECT * FROM `items` WHERE `id`= LAST_INSERT_ID()
mysql
MyIsam engine transaction support
MyISAM effectively works in auto-commit mode (as it’s not a transactional engine), and it just ignores the commit/rollback. Actually storage engine is a different layer in the MySQL architecture, separated from the SQL parser, the SQL layer communicates to the storage engine with lower-level API, and that’s the reason there is a common SQL and … Read more
MySQL – SQL_BIG_SELECTS
MySQL determines whether or not a query is a ‘big select’ based on the value of ‘max_join_size’. If the query is likely to have to examine more than this number of rows, it will consider it a ‘big select’. Use ‘show variables’ to view the value of the max join size. I believe that indexing … Read more
What is difference between storing data in a blob, vs. storing a pointer to a file?
I read that the data type can be used to store files. According to MySQL manual page on Blob, A BLOB is a binary large object that can hold a variable amount of data. Since it’s a data type specific to store binary data it’s common to use it to store files in binary format, … Read more
What is the best MySQL collation for German language
This answer is outdated. For full emoji support, see this answer. As the character set, if you can, definitely UTF-8. As the collation – that’s a bit nasty for languages with special characters. There are various types of collations. They can all store all Umlauts and other characters, but they differ in how they treat … Read more
Defining Composite Key with Auto Increment in MySQL
You can’t have MySQL do this for you automatically for InnoDB tables – you would need to use a trigger or procedure, or use another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key. Something like the following should work DELIMITER $$ CREATE TRIGGER xxx BEFORE INSERT ON … Read more
Drop Column from Large Table
Anything that you do is going to require reading and writing 38m rows, so nothing is going to be real fast. Probably the fastest method is to put the data into a new table: create table newTable as select id1, id2 from oldTable; Or, if you want to be sure that you preserve types and … Read more
update command is denied for user
For everyone who have tried answering this question here is my sincere thanks. I have found the problem and solution. my sql query is like this UPDATE `dblayer`.`test` SET `title` = ‘hello a’ WHERE `test`.`id` =1; which I got from phpmyadmin and it works perfectly on my system. But when I work on the servers … Read more
How to produce “phantom read” in REPEATABLE READ? (MySQL)
The “phantom read” in MySQL on RR isolation level is hidden deep, but still can reproduce it. Here are the steps: create table ab(a int primary key, b int); Tx1: begin; select * from ab; // empty set Tx2: begin; insert into ab values(1,1); commit; Tx1: select * from ab; // empty set, expected phantom … Read more
mySQL convert varchar to date
select date_format(str_to_date(’31/12/2010′, ‘%d/%m/%Y’), ‘%Y%m’); or select date_format(str_to_date(’12/31/2011′, ‘%m/%d/%Y’), ‘%Y%m’); hard to tell from your example