To get all birthdays in next 7 days, add the year difference between the date of birth and today to the date of birth and then find if it falls within next seven days. SELECT * FROM persons WHERE DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0) YEAR) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY); If … Read more
const ModelIncrementSchema = new Schema({ model: { type: String, required: true, index: { unique: true } }, idx: { type: Number, default: 0 } }); ModelIncrementSchema.statics.getNextId = async function(modelName, callback) { let incr = await this.findOne({ model: modelName }); if (!incr) incr = await new this({ model: modelName }).save(); incr.idx++; incr.save(); return incr.idx; }; const … Read more
A crude (but effective) way of doing it would be to dump the schema into a file, carefully apply the search-and-replace and then re-import. As a matter of fact I did that today 🙂
There is another solution, if you have binary logs active on your server you can use mysqlbinlog generate a sql file with it mysqlbinlog binary_log_file > query_log.sql then search for your missing rows. If you don’t have it active, no other solution. Make backups next time.
Absolutely it is safe to do this. This is how client-server applications work. If you are using a three-tier application, the application server will keep a pool of connections open anyway. Scalability is an issue, or at least used to be in the days that machines had less memory than modern kit. With a two-tier … Read more
If it’s part of a Database query you should be able to use a Parameterized SQL Statement. As well as escaping your quotes, this will deal with all special characters and will protect you from SQL injection attacks.
I don’t know of any performance penalties in frequent closing/opening of the database (regardless of its size). I think the answer to this question also depends on what type of application is accessing the database. Do you “re-query” the database a lot? Then it seems rectified to keep it open. Do you fetch different data … Read more
You are assuming that the current working directory is the same as the directory your script lives in. It is not an assumption you can make. Your script is opening a new database in a different directory, one that is empty. Use an absolute path for your database file. You can base it on the … Read more
Each tablespace has one or more datafiles that it uses to store data. The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile. To find out if the actual limit is 32gb, run the following: … Read more
I have done various audit schemes over the years and I am currently going to implement something like this: Person ———————————————— ID UINT NOT NULL, PersonID UINT NOT NULL, Name VARCHAR(200) NOT NULL, DOB DATE NOT NULL, Email VARCHAR(100) NOT NULL Person_History ———————————————— ID UINT NOT NULL, PersonID UINT NOT NULL, Name VARCHAR(200) NOT NULL, … Read more