I need to populate a currently empty table with a hundred or so fake records to simulate logins over the past two years to test my code with.
The login table schema looks like:
CREATE TABLE `Logins` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`User_ID` i开发者_Python百科nt(11) NOT NULL,
`Date_Login` datetime NOT NULL,
`Location` enum('site','admin') NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I really am new at SQL in general, so, I don't have the slightest idea what the query should look like past
INSERT INTO `Logins` (`User_ID`,`Date_Login`,`Location`) VALUES ...
What I need is insert N entries (lets say 100) into Logins
so that
User_ID
draws its values from theUsers
table'sID
fieldDate_Login
should be between 2 years ago and nowLocation
should alternate between'site'
and'admin'
, but with'site'
more heavily weighted (say 80% of the time).
Hopefully, I can glean some SQL-fu to help with similar problems in the future :D
Thanks!
(I'm using MySQL 5.1)
Here is an SQL statement to insert a single row into the Logins table. You can run this repeatedly (e.g. in a stored procedure) to get multiple records. You have to run it multiple times to get multiple records because if you increase LIMIT 1
to LIMIT 10
you will get 10 records but the User_ID
values will be the same for each record.
INSERT INTO `Logins` (`User_ID`, `Date_Login`, `Location`)
SELECT
users.ID AS `User_ID`,
DATE_SUB(NOW(), INTERVAL FLOOR(1 + (RAND() * (365 * 2))) DAY) AS `Date_Login`,
IF(RAND() > 0.8, 'admin', 'site') AS `Location`
FROM users
ORDER BY RAND()
LIMIT 1;
Normally ORDER BY RAND()
is bad style because it is inefficient, but this isn't a performance-sensitive task.
If you want to use a full SQL solution, here is how you can create a loop: http://dev.mysql.com/doc/refman/5.1/en/iterate-statement.html To get a random date, you can use RAND() in combination with +, * and some timestamps.
精彩评论