We would like to introduce Wordpress into our cloud environment. We have 3 tiers of development severs and the production tier. Our environment is as follows: Dev dev.mysite.com, Test test.mysite.com, Stage stage.mysite.com, Prod mysite.com
We need a clean way to migrate the site through each environment without having to use an update query or script such as https://interconnectit.com/products/search-and-replace-for-wordpress-databases/. Particularly in the stage to production migration phase, where the code is signed off and frozen. Running such a query after code freeze is against company policy.
Is there a way to set the site URL to mysite.com and dynamically prep-end the appropriate sub-domain based on the server environment?
You can handle prepending the subdomain for the site URLs as described in the StackOverflow answer referred to in the comments, but WordPress has lots of URLs stored in the database that would not be changed by anything but a database UPDATE or lots of WordPress filters.
For example, if a user uses the standard WordPress 'Add Media" button to insert an image into a post or page, the resulting image URL is absolute. You would need to have some way of insuring that the users always changed those URL to relative links, or images would be broken after migration.